Forum Discussion

adamw1984's avatar
adamw1984
Regular Visitor
9 months ago
Solved

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 visu...
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    9 months ago

    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

Ritaf1983's avatar
Ritaf1983
Super User
9 months ago

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