Forum Discussion

knotpc's avatar
knotpc
Icon for Advocate I rankAdvocate I
6 years ago
Solved

Row totals for basic NestIF not totaling as expected.

Hoping the gurus on Power BI can help. Also, open to alternative methods. 

 

I have a measure "Predicted Revenue EoY = IF([Revenue]>[ForecastRevenue],[Revenue],[ForecastRevenue])". The measure is fairly straight forward and it calculates the values correctly for each row. However, when used in a table the SUM of the totals is totally wrong. Not sure what I am doing wrong with this one. 

 

For background here is what I have in terms of measures all of which are in a "Formulas Table". 

 

Invoiced = SUM(Sales[Invoiced]) "This table is the sum of all revenue that has been invoiced"

Revenue + Backlog = SUM(Sales[Revenue])+SUM(Backlog[Revenue]) "This measure combines what has been invoiced with the backlog of orders."

Forecast Revenue = SUM(Forecast[Revenue])

Predicted Revenue EoY = IF([Revenue]>[ForecastRevenue],[Revenue],[ForecastRevenue])

 

All of the tables above a linked to a Calendar Table. 

 

Below is an example of what the measures are returning. (Real values replaced to protect the innocent. :)

 

As you can see Invoiced, Revenue+Backlog and Forecast Revenue SUM correctly. For some reason, the Predicted Revenue EoY is not summing correctly as it matches the Revenue+Backlog sum even though it shows values for November and December. The sum should be $17000, not $11000.  

 

Hoping some guru can tell me what I am doing wrong on this one. 

 

 

Need Help

4 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    knotpc there are many blogs and post on this and here is link to one. Hope this helps.

    • knotpc's avatar
      knotpc
      Icon for Advocate I rankAdvocate I
      6 years ago

      Parry2K, 

       

      Thank you for the link. It led me down the path to understanding that the measure I wrote was dead wrong for the use case. So back to the drawing board I go. 

       

      Clint. 

  • kentyler's avatar
    kentyler
    Icon for Solution Sage rankSolution Sage
    6 years ago

    Remember, the value in the "totals" row is not the values from the forecast revenues column added up.

    Your measure calculates the value for each row because it is being filtered for just that row.

    In the totals row there is no filter, so the measure adds all the values for all the rows together.

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    knotpc ,

     

    Could you please share the two tables 'Sales' and 'Forecast' and show the expected result?

     

    Regards,

    Jimmy Tao