Skip to main content
Solved

How to make semi-additive measures easier to handle?

  • March 14, 2023
  • 1 reply
  • 366 views

Forum|alt.badge.img+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!!

Best answer by francois

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!

View original
Did this topic help you find an answer to your question?

francois
Employee
Forum|alt.badge.img+13
  • Employee
  • March 14, 2023

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


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings