Forum Discussion
Sum Measure Calculations in Matrix Totals
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.
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=yw0QHu9V4UQhttps://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
2 Replies
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=yw0QHu9V4UQhttps://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
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
