Solved

How to make semi-additive measures easier to handle?

  • 14 March 2023
  • 1 reply
  • 323 views

Badge +1

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!!

icon

Best answer by francois 14 March 2023, 21:05

View original

1 reply

Userlevel 6
Badge +14

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