Sum Measure Calculations in Matrix Totals

[adamw1984]

Hi,

I have created a measure ('Price Effect') that is the product of two other measures ('ASP Delta' and 'Qty CY'), which gives me a correct result on the lowest row level (product) of a matrix visualsation where I have customer, category and product levels included.

My issue is that for the customer, category and Total rows of the visualisation the same calculation is applied (ASP Delta * Qty CY), when instead I want to show the sum of the results of the product levels.

Below shows the results I'm getting currently.

The example below shows a comparison of the results I get in power BI ('Price effect' column), compared to what I want to achieve ('Correct Price Effect' column):

Is there anyway to modify the calculation so that I can show the sum of the lower level results on the total/subtotal rows instead of applying the same calculation? 
This is very easy in other tools such as Tableau where you can just select the total row calculation types within the visualization but I haven't been able to find how to do this in power BI.

[v-pgoloju]

Hi @adamw1984,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @Ritaf1983  for prompt and helpful response. Please try below dax to resolve the issue.

PriceEffect = SUMX(SUMMARIZE(
'Sales',
'Sales'[Customer],
'Sales'[Category],
'Sales'[Product],
"RowPriceEffect", [ASP Delta] * [Qty CY]),[RowPriceEffect])

Thanks & Regards,

Prasanna Kumar

[Ritaf1983 · Accepted solution]

Hi @adamw1984 

In Power BI, the total and subtotal rows in tables or matrices don’t represent a direct sum of the rows above. Instead, they are recalculations in a different context. When Power BI reaches a total row, the DAX engine removes the filters applied at the row level and evaluates the measure again over the entire data scope.

The solution is to build a custom calculation context within the formula, for example:

SUMX(
VALUES('Product'[Product]),
[ASP Delta] * [Qty CY]
)

This forces the total to sum up the product-level results, even when the visual aggregates by category or customer.
For deeper guidance, it would help if you could share a simplified PBIX file or a sample of the data along with the expected outcome. That would make it easier to provide a precise DAX solution matching your mod.

More information about totals challnges are here:
https://www.youtube.com/watch?v=yw0QHu9V4UQ

https://www.youtube.com/watch?v=gYzszodBotc

https://www.youtube.com/watch?v=KNkLIUIb2Lo

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly