topic Re: Calculation ordering question in DAX Commands and Tips

Calculation ordering question

robbielambert — Wed, 15 Jul 2020 16:38:14 GMT

Hi, thanks in advance for the help on this.

I have two measures which are quite complex. that yield results at a row level that look something like this:

Account Id	Measure A	Measure B
A1	3	2
A2	5	9
A3	7	6
A4	2	3

I need to calculate a new meaure which is sum(Measure A - Measure B).

But for each row, if Measure A - Measure B is less than zero, I want to set the result to zero, so the desired result would be:

A1: 3 - 2 = 1

A2: 5 - 9 = 0

A3: 7 - 6 = 1

A4: 2 - 3 = 0

Total = 2

How do I instruct power bi to calculate the measures at a row level applying the zero rule first, then aggregate, rather than aggregating then applying the zero rule.

Thanks

Robbie

Re: Calculation ordering question

Anonymous — Wed, 15 Jul 2020 16:54:39 GMT

Hi robbielambert ,

Suppose your measures are Measure AA and Measure BB

Diff of Measure = SUMX('Table',[Measure AA] - [Measure BB])

Final Measure = SUMX('Table', IF ([Diff of Measure] < 0 ,0, [Diff of Measure]))

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

Re: Calculation ordering question

robbielambert — Wed, 15 Jul 2020 17:03:28 GMT

Hi Harsh,

Works like a treat!

You are a scholar and a gent thank you!

Robbie