Forum Discussion
Calculation ordering question
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
- Anonymous6 years ago
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!
2 Replies
- AnonymousNot applicable
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!
- robbielambertFrequent Visitor
Hi Harsh,
Works like a treat!
You are a scholar and a gent thank you!
Robbie