Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
msimmonsmcse
Helper I
Helper I

Matrix rows not summing properly

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,
            0
        )
    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. 
1 ACCEPTED SOLUTION
Greg_Deckler
Community Champion
Community Champion

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

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
v-vpabbu
Community Support
Community Support

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

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

Hi @msimmonsmcse,

 

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

Greg_Deckler
Community Champion
Community Champion

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

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.