Forum Discussion

robbielambert's avatar
robbielambert
Frequent Visitor
6 years ago
Solved

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 IdMeasure AMeasure B
A132
A259
A376
A423

 

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

  • Anonymous's avatar
    Anonymous
    6 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

  • Anonymous's avatar
    Anonymous
    Not 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!

     

     

     

    • robbielambert's avatar
      robbielambert
      Frequent Visitor

      Hi Harsh,

       

      Works like a treat!

       

      You are a scholar and a gent thank you!

       

      Robbie