Skip to main content

Hi everyone, I'm facing an issue for creating metrics that are on a "per basis". They're divisions between 2 sums.

Those 2 sums (let's say A and 😎 are defined across the same 6 dimensions, and individually work fine and aggregate as sums on any visualization with them even if I pick let's say 3/6 dimensions in which they exist

However when I create a metric dividing those metrics A and B, we face issues when displaying on a table 3/6 dimensions, because there's no option to define the order of operations, and what happens is:

- The table calculates A / B on each combination of 6 dimensions
- Aggregate as sum of (A / 😎 on the table at display

What needs to happen is:
- Calculate sum of A
- Calculate sum of B
- Divide sum of A by sum of B

And we couldn't find a formula to do this, or make explicitly how we want to aggregate.

Those are semi-additive measures, and maybe this example on DAX helps:
https://www.sqlbi.com/articles/semi-additive-measures-in-dax/

Thanks!!

Hi @Armando Bitt 

Have you looked into the Show Value As feature? It should fit your needs.

Basically you should add Metrics A and B in your table, and then Show Value As > % of another Metric > Metric A.

That way Pigment still calculates the individual sum of items and then does the ratio on the fly.

Hope this helps!


Reply