cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

Row subtotals in Matrix not adding up the correct ammounts

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?

1 ACCEPTED SOLUTION
Super User

@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?

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Super User

@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?

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.