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:
Best answer by francoisView original