Row totals for basic NestIF not totaling as expected.

[knotpc]

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. 

[v-yuta-msft]

@knotpc ,

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

Regards,

Jimmy Tao

[kentyler]

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.

[parry2k · Accepted solution]

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

[knotpc]

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.