topic Re: Matrix rows not summing properly in DAX Commands and Tips

Matrix rows not summing properly

msimmonsmcse — Thu, 03 Apr 2025 20:57:53 GMT

I have the measure below that is calculating everything properly on a month by month context. But when I put it in a matrix with the Billing Groups in rows, the Total columns are not summing all the values. I have tried adding DIM_CALENDAR[YYYYMM] to the SUMMARIZECOLUMNS filter, and wrapping the whole thing in another SUMX function using SUMMARIZECOLUMNS( DIM_CALENDAR[YYYYMM] ) for the table context. Neither works right.

SUMX(

SUMMARIZECOLUMNS(

DIM_EMPLOYEE[Billing Group],

'Agreement Additions'[AGR Header RecID]

VAR DateStart = MIN(DIM_CALENDAR[Date])

VAR DateEnd = MAX(DIM_CALENDAR[Date])

VAR AGR_Header_RecID = 'Agreement Additions'[AGR Header RecID]

VAR BillingGroup = DIM_EMPLOYEE[Billing Group]

VAR Product_RecID_Filter =

SWITCH(

TRUE(),

BillingGroup = "Group 1", 958,

BillingGroup = "Group 2", 956,

BillingGroup = "Group 3", 1133,

BillingGroup = "Group 4", 869,

)

VAR ExtPrice =

CALCULATE(

FIRSTNONBLANK('Agreement Additions'[Allocation], 1),

'Agreement Additions'[Billing Group RecId] = Product_RecID_Filter,

Agreements[AGR Header RecID] = AGR_Header_RecID,

Agreements[AGR Type RecID] = 30,

Agreements[Date Start] <= DateEnd,

(ISBLANK(Agreements[Date End]) || Agreements[Date End] >= DateStart),

(ISBLANK('Agreement Additions'[Cancelled Date]) || 'Agreement Additions'[Cancelled Date] >= DateStart)

)

RETURN

ExtPrice

)

For the model, Agreements and Agreement Additions tables are related one to many on the AGR Hearder RecID columns, but the are not related to any of the other tables in the measure.

Re: Matrix rows not summing properly

Greg_Deckler — Thu, 03 Apr 2025 21:14:21 GMT

msimmonsmcse First, please vote for this idea: https://community.fabric.microsoft.com/t5/Fabric-Ideas/Matrix-Table-grand-totals-with-Measures/idi-p/4475982

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

Re: Matrix rows not summing properly

Anonymous — Mon, 07 Apr 2025 12:47:50 GMT

Hi msimmonsmcse,

Thanks Greg_Deckler for Addressing the issue.

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

Re: Matrix rows not summing properly

Anonymous — Fri, 11 Apr 2025 13:54:50 GMT

Hi msimmonsmcse,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

Re: Matrix rows not summing properly

Anonymous — Mon, 14 Apr 2025 13:30:43 GMT

Hi @msimmonsmcse,

Regards,
Vinay Pabbu