Row subtotals in Matrix not adding up the correct ammounts

[IVIr_D]

I have a matrix with a few things in there like budgets and Committed Costs etc. but the row subtotals are not displaying correctly.

In the Screenshot below you will see the Forecast Final Cost column where I used the following DAX formula to calculate it:

Forecast Final Cost =

IF(

    '*Forecast Purchase Orders - Areas and Transport'[Committed Costs Total]>'Forecast Budgets - Areas'[Revised Budget],

    SUM('*Forecast Purchase Orders - Areas and Transport'[Order Total Excl]) + SUM('Forecast Return To Suppliers - Areas and Transport'[Amount Excl]),

    IF(

        SUM('Forecast Budgets - Areas'[Forecast to Come Manual])>=0.01,

        [Committed Costs Total]+SUM('Forecast Budgets - Areas'[Forecast to Come Manual]),

        [Committed Costs Total]+'Forecast Budgets - Areas'[Forecast to Come Calc]

        )

)

If you add the amounts in the column up they are not the same as in the screenshot, why is this and is there a fix for it?

 

[AllisonKennedy · Accepted solution]

@IVIr_D  this is because your [Committed Costs Total] compared to your [Revised Budget] at the total level has only one value for committed costs, and one value for budget. So the IF statement will be evaluated and either do the first option, or proceed to the next IF statement. In your table, the non total level will have some with budget greater than cost and some the other way around, but the total will be all or nothing. It's all about the 'evaluation context' of your DAX.

One way to fix it is to wrap your measure in a SUMX:

Forecast Final Cost Totals fix = 

SUMX ( AreaMasterDescriptionTable , [Forecast Final Cost] ) 

Note, the 'AreaMasterDescriptionTable' in my measure above must be the same level of granularity as your table visual. Hope that makes sense?