Forum Discussion
Custom Totals Based on Matrix Levels
Hello,
I'm working with Power BI and have a measure that returns different calculations depending on the level in the matrix:
Level 1 is "Class"
Level 2 is "Group"
Both fields come from my Grouping dimension table, where the column "Group" is the primary key linked to my fact table.
My current measure works fine at the row level inside the matrix. Here's a simplified version of the DAX:
'''VAR total =
CALCULATE(
SUM(Fact[Amount]),
Fact[Group] = MAX(Fact[Group]),
Fact[Type] = MAX(Fact[Type]),
Fact[Date] = MAX(Fact[Date])
)
RETURN
IF(
ISINSCOPE(Groupping[Class]) && NOT ISINSCOPE(Groupping[Group]),
total,
IF(
MAX(Groupping[Type]) = "A" && ISINSCOPE(Groupping[Group]),
SUM(Fact[Amount]) * 0.6
)
)'''
What I need to do now is fix the totals:
I want a measure that shows the same result as above in the rows, but in the total, it should sum what we see at the Class level.
I also want another measure that returns the same values in the rows, but in the total, it should sum what appears at the Group level.
I tried modifying the measure to detect when we're at the total level (by checking if we're not in any level) and used SUMX(VALUES(Groupping[Class]), ...) or SUMX(VALUES(Groupping[Group]), ...) to reapply the logic, but I’m still not getting the correct totals.
- How can I force the total to reflect the sum of values I see at a specific matrix level (Class or Group)?
Any help would be much appreciated!
Thank you.
Hi NT2 - Create Base Measure (Row Logic)
Custom Amount =
VAR total =
CALCULATE(
SUM(Fact[Amount]),
Fact[Group] = MAX(Fact[Group]),
Fact[Type] = MAX(Fact[Type]),
Fact[Date] = MAX(Fact[Date])
)
RETURN
IF(
ISINSCOPE(Groupping[Class]) && NOT ISINSCOPE(Groupping[Group]),
total,
IF(
MAX(Groupping[Type]) = "A" && ISINSCOPE(Groupping[Group]),
SUM(Fact[Amount]) * 0.6
)
)This measure works row-by-row — do not use this for the total row.
If you want the total of what shows at the Class level, do this
Custom Amount (Class Total) =
IF(
HASONEVALUE(Groupping[Class]) || ISINSCOPE(Groupping[Group]),
[Custom Amount], -- Keep row-level logic
SUMX(
VALUES(Groupping[Class]),
CALCULATE([Custom Amount])
)
)try to fix the , total at the Group level
Custom Amount (Group Total) =
IF(
ISINSCOPE(Groupping[Group]),
[Custom Amount], -- At group row, show the usual logic
SUMX(
VALUES(Groupping[Group]),
CALCULATE([Custom Amount])
)
)Try the above and let know.
Hi NT2,
Thank you for reaching out to the Microsoft fabric community forum. Thank you rajendraongole1, for your inputs on this issue.
After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.
Dax Measure for Measure_WithClassTotal in Fact table:Measure_WithClassTotal = VAR CurrentValue = [Measure_Base] VAR IsTotalRow = NOT ISINSCOPE(Groupping[Class]) && NOT ISINSCOPE(Groupping[Group]) RETURN IF( IsTotalRow, SUMX( VALUES(Groupping[Class]), CALCULATE([Measure_Base]) ), CurrentValue )
outcome:I am also including .pbix file for your better understanding, please have a look into it:
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.
3 Replies
Hi NT2 - Create Base Measure (Row Logic)
Custom Amount =
VAR total =
CALCULATE(
SUM(Fact[Amount]),
Fact[Group] = MAX(Fact[Group]),
Fact[Type] = MAX(Fact[Type]),
Fact[Date] = MAX(Fact[Date])
)
RETURN
IF(
ISINSCOPE(Groupping[Class]) && NOT ISINSCOPE(Groupping[Group]),
total,
IF(
MAX(Groupping[Type]) = "A" && ISINSCOPE(Groupping[Group]),
SUM(Fact[Amount]) * 0.6
)
)This measure works row-by-row — do not use this for the total row.
If you want the total of what shows at the Class level, do this
Custom Amount (Class Total) =
IF(
HASONEVALUE(Groupping[Class]) || ISINSCOPE(Groupping[Group]),
[Custom Amount], -- Keep row-level logic
SUMX(
VALUES(Groupping[Class]),
CALCULATE([Custom Amount])
)
)try to fix the , total at the Group level
Custom Amount (Group Total) =
IF(
ISINSCOPE(Groupping[Group]),
[Custom Amount], -- At group row, show the usual logic
SUMX(
VALUES(Groupping[Group]),
CALCULATE([Custom Amount])
)
)Try the above and let know.
Hi NT2,
Thank you for reaching out to the Microsoft fabric community forum. Thank you rajendraongole1, for your inputs on this issue.
After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.
Dax Measure for Measure_WithClassTotal in Fact table:Measure_WithClassTotal = VAR CurrentValue = [Measure_Base] VAR IsTotalRow = NOT ISINSCOPE(Groupping[Class]) && NOT ISINSCOPE(Groupping[Group]) RETURN IF( IsTotalRow, SUMX( VALUES(Groupping[Class]), CALCULATE([Measure_Base]) ), CurrentValue )
outcome:I am also including .pbix file for your better understanding, please have a look into it:
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.Hi NT2,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.