Matrix rows not summing properly

[msimmonsmcse]

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. 

[v-vpabbu]

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

[v-vpabbu]

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

[v-vpabbu]

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 · Accepted solution]

@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