Matrix subtotal showing incorrect totals when using TREATAS() to map Allocation Units per employee

[SBC]

Hi,

I am working on a Power BI matrix visual where I need to display Allocation Units per employee and also show correct subtotals at the department level and grand total level.

Matrix Structure

Rows:

• DepartmentName

• EmployeeName

Values:

• Allocation Units (measure)

Tables Used

Table 1: Activity_Log_Table
Contains:

• DepartmentName

• EmployeeName

Table 2: Resource_Allocation_Table
Contains:

• StaffFullName

• AllocationUnits

There is no direct relationship between these tables, so I am using TREATAS() to map EmployeeName with StaffFullName.

Current Measure

Allocation Units Measure =
CALCULATE(
MAX('Resource_Allocation_Table'[AllocationUnits]),
TREATAS(
VALUES('Activity_Log_Table'[EmployeeName]),
'Resource_Allocation_Table'[StaffFullName]
)
)

Problem

The measure returns correct values at the EmployeeName level, but:

• DepartmentName subtotal is incorrect

• Grand total is incorrect

• Power BI recalculates totals instead of summing visible rows

Expected Behaviour

I want the matrix totals to behave like Excel-style aggregation:

• Show correct allocation units per EmployeeName

• Show subtotal per DepartmentName as sum of EmployeeName values

• Show correct grand total as sum of all EmployeeName values

• Avoid duplicate counting when EmployeeName appears multiple times

Sample Data

Activity_Log_Table

DepartmentName	EmployeeName
Orion	Alex M
Orion	Nina P
Orion	Kevin L
Nova	Daniel R
Nova	Sophia T

Resource_Allocation_Table:

StaffFullName	AllocationUnits
Alex M	40
Nina P	25
Kevin L	15
Daniel R	35
Sophia T	20

Expected Matrix Output

DepartmentName	EmployeeName	Allocation Units
Orion	Alex M	40
Orion	Nina P	25
Orion	Kevin L	15
Orion Total		80
Nova	Daniel R	35
Nova	Sophia T	20
Nova Total		55
Grand Total		135

Currently, subtotals and grand totals are not matching the expected summed values.

What is the correct DAX approach to ensure totals aggregate properly when using TREATAS() inside a matrix visual?

Thanks,

SBC

[Ray_Minds]

Solution: This error is usually not related to DAX or report logic -
it’s a temporary Power BI Service issue. It can happen due to session timeout, dataset connectivity issues, or sometimes just a backend glitch.
Quick things to try:
* Refresh the page or open in Incognito.
* Logout and login again.
* Try a different browser.
If it still doesn’t work, then check:
* Dataset refresh / credentials
* Workspace access or permissions
If the issue continues, share the Exception ID with your admin - they can trace it from backend logs.

[Kedar_Pande]

@SBC 

Fixed measure

Allocation Units = 
SUMX(
VALUES('Activity_Log_Table'[EmployeeName]),
CALCULATE(
MAX('Resource_Allocation_Table'[AllocationUnits]),
TREATAS(
'Activity_Log_Table'[EmployeeName],
'Resource_Allocation_Table'[StaffFullName]
)
)
)

Test it - subtotals match expected exactly

[SBC · Accepted solution]

Thanks Everyone, I was able to resolve the issue by modifying the measure so that totals are calculated using an explicit row-level aggregation instead of relying on the default evaluation context inside the matrix visual.

The problem occurred because the matrix subtotal and grand total were being recalculated rather than summing the visible employee-level values when using TREATAS().

Below is the working solution:

Allocation Units Measure =
SUMX(
SUMMARIZE(
CALCULATETABLE(
'Resource_Allocation_Table',
TREATAS(
VALUES('Activity_Log_Table'[EmployeeName]),
'Resource_Allocation_Table'[StaffFullName]
)
),
'Resource_Allocation_Table'[StaffFullName],
'Resource_Allocation_Table'[AllocationUnits]
),
'Resource_Allocation_Table'[AllocationUnits]
)

After applying this logic, both department-level subtotals and the overall total are now aggregating correctly in the matrix visual.

Sharing this here in case it helps others facing similar subtotal issues when using TREATAS().

Thanks,

SBC