Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Power BI Report mathematics issue

Greetings,

I created a report for our Finance department and have had an anamoly point out. 

 

The issue arises with this Dax Measure: 

Mar Rebate = Sum('Brand Partners'[Rebate %])*Sumx('Sales History',[Mar Rebatable])/100
In our business we invoice our Brand Partners a percentage of Rebatable sales (of these three they are 2%, but if varies), and a Management Fee (of these three they are 1%) of Gross Sales.
 
As highlighted in Pink, both the Rebate (2% of $1,251,953.52) is correct at $25,039.07. However in the second line the Rebate amount which is supposed to be 2% of $637,571.48 is incorrect showing $13,170.93 (correct 2% is $12,751.43), additionally the 1% calculation is incorrect at $6,585.46 (should be $6,375.71)
 
This is one report with differing results using these Dax measures:
Mar MF = Sum('Brand Partners'[Management Fee %])* SUMX('Sales History','Sales History'[Mar Gross])/100 (Manufacters Fee calculation)
 
Mar Rebate = Sum('Brand Partners'[Rebate %])*Sumx('Sales History',[Mar Rebatable])/100 (Rebate calculation)
 
 Do we have a bug or what are your thoughts?
Need Help
  • speedramps's avatar
    speedramps
    4 years ago

    SUMX is an iterator

    So 2% of [Mar Rebatable] is not the necessary the same as 2% of SUMX('Sales History',[Mar Rebatable]).
    There is a quick way to check this.
    Create a new measure Mar Rebatable using SUMX = Sumx('Sales History',[Mar Rebatable])
    Then compare Mar Rebatable with  Mar Rebatable using SUMX.

     

    Consider resolving the problem with

    Mar Rebate = Sum('Brand Partners'[Rebate %]) *[Mar Rebatable])/100

     

3 Replies

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    4 years ago

    SUMX is an iterator

    So 2% of [Mar Rebatable] is not the necessary the same as 2% of SUMX('Sales History',[Mar Rebatable]).
    There is a quick way to check this.
    Create a new measure Mar Rebatable using SUMX = Sumx('Sales History',[Mar Rebatable])
    Then compare Mar Rebatable with  Mar Rebatable using SUMX.

     

    Consider resolving the problem with

    Mar Rebate = Sum('Brand Partners'[Rebate %]) *[Mar Rebatable])/100

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    A simple mistake, readily corrected....best!