Matrix difference in total with grouping category

[JKG]

I have a matrix that looks like the following:

Thanks to @About v-lionel-msft - Microsoft Power BI Community, I found a formula to calculate the difference rather than the total.  

However, my problem now is that when I add a grouping category (from another table)  to the rows it repeats all the paycodes and does not recognize the group.

 Can anyone give me an idea of how to fix the below formula so that the YoY difference will recognize the grouping?

YoY Difference =

VAR x =

CALCULATE(

SUM('Pilot Payroll Detail'[Total_CHs_Paid]),

FILTER(

ALLSELECTED('Pilot Payroll Detail'),

'Pilot Payroll Detail'[BidMnth] < MAX('Pilot Payroll Detail'[BidMnth]) && 'Pilot Payroll Detail'[PayCode] = MAX('Pilot Payroll Detail'[PayCode])

)

)

VAR y =

CALCULATE(

SUM('Pilot Payroll Detail'[Total_CHs_Paid]),

FILTER (ALL ('Pilot Payroll Detail'), 'Pilot Payroll Detail'[BidMnth]= MAX('Pilot Payroll Detail'[BidMnth]) && 'Pilot Payroll Detail'[PayCode] = MAX('Pilot Payroll Detail'[PayCode]))

)

RETURN

IF(

HASONEFILTER('Pilot Payroll Detail'[BidMnth]),

SUM('Pilot Payroll Detail'[Total_CHs_Paid]),

y-x

)

Thank you!

[v-yingjl]

Hi @JKG ,

Not certain what is the relationship between your another table and this sample table, please check whether you have enabled 'show item with no data' first.

 

Then I noticed that each total value is wrong when you add the type field to the matrix, based on my test sample, you can try to modify the measure like this:

m =
VAR x =
    CALCULATE (
        SUM ( 'Pilpt Payroll Detail'[Total_CHs_Paid] ),
        FILTER (
            ALL ( 'Pilpt Payroll Detail' ),
            'Pilpt Payroll Detail'[BidMnth] < MAX ( 'Pilpt Payroll Detail'[BidMnth] )
                && 'Pilpt Payroll Detail'[PayCode] IN DISTINCT ( 'Pilpt Payroll Detail'[PayCode] )
        )
    )
VAR y =
    CALCULATE (
        SUM ( 'Pilpt Payroll Detail'[Total_CHs_Paid] ),
        FILTER (
            ALL ( 'Pilpt Payroll Detail' ),
            'Pilpt Payroll Detail'[BidMnth] = MAX ( 'Pilpt Payroll Detail'[BidMnth] )
                && 'Pilpt Payroll Detail'[PayCode] IN DISTINCT ( 'Pilpt Payroll Detail'[PayCode] )
        )
    )
RETURN
    IF (
        HASONEFILTER ( 'Pilpt Payroll Detail'[BidMnth] ),
        SUM ( 'Pilpt Payroll Detail'[Total_CHs_Paid] ),
        IF ( ISINSCOPE ( 'Table'[Type] ), y - x, y - x )
    )

Attached my sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JKG]

I tried your solution and it is very close to working.  The categories show up correctly, but now the total is picking up the difference of the category for the entire data set rather than for the filtered months. 

[v-yingjl]

Hi @JKG ,

Could you please consider sharing a simple sample file without any sesentive information for further discussion?

Best Regards,
Community Support Team _ Yingjie Li