topic Re: Matrix subtotal showing incorrect totals when using TREATAS() to map Allocation Units per employee in DAX Commands and Tips

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

SBC — Fri, 27 Mar 2026 03:46:54 GMT

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

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

SBC — Fri, 27 Mar 2026 05:29:54 GMT

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

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

Kedar_Pande — Fri, 27 Mar 2026 06:53:59 GMT

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

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

Ray_Minds — Thu, 02 Apr 2026 06:05:43 GMT

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.