Forum Discussion

msimmonsmcse's avatar
msimmonsmcse
Helper I
1 year ago
Solved

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. 
"aggregations"

4 Replies