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 B) 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 / B) 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!!
Solved
How to make semi-additive measures easier to handle?
+1- Newly Minted Pigmenteer
Best answer by francois
Hi
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!
+13- Employee
Hi
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!
François (Pigment)
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.