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!!
How to make semi-additive measures easier to handle?
+1- Apprentice Author
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
- Answer
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!
Log into the Pigment Community
No account yet? Create an account
Single Sign-On Need help?
Log in with your Pigment accountEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.