DAX - Matrix Tale Difference - Selected Value (Slicer)

[Anonymous]

Hi Team,

I have created Matric table for FY 24 and 25,26.

and I need difference for the selected month like Table A and Table B, (both are lookup from mail table)

selected month wise FY difference needed, kindly advise me.

Thanks,

w_D

[Anonymous · Accepted solution]

Hi @Anonymous 

Thanks for the solution @johnbasha33  provided, and i want to offer some more information for you to refer to.

Sample data 

Table A

Table B

1.Create a type table

Type =
VAR A =
    SUMMARIZE (
        ADDCOLUMNS (
            CROSSJOIN ( VALUES ( 'Table'[Month] ), { "FY24", "FY25", "FY26" } ),
            "Month_FY",
                [Month] & "-" & [Value]
        ),
        [Month_FY]
    )
VAR B = { "Difference-FY24", "Difference-FY25", "Difference-FY26" }
RETURN
    UNION ( A, B )

2.Create a measure

MEASURE =
VAR a =
    LEFT (
        MAX ( 'Type'[Month_FY] ),
        SEARCH ( "-", MAX ( 'Type'[Month_FY] ),, BLANK () ) - 1
    )
RETURN
    IF (
        NOT ( CONTAINSSTRING ( SELECTEDVALUE ( 'Type'[Month_FY] ), "Difference" ) )
            && OR ( a IN VALUES ( 'Table A'[Month] ), a IN VALUES ( 'Table B'[Month] ) ),
        SWITCH (
            TRUE (),
            CONTAINSSTRING ( MAX ( 'Type'[Month_FY] ), "FY24" ), CALCULATE ( SUM ( 'Table'[FY24] ), 'Table'[Month] = a ),
            CONTAINSSTRING ( MAX ( 'Type'[Month_FY] ), "FY25" ), CALCULATE ( SUM ( 'Table'[FY25] ), 'Table'[Month] = a ),
            CONTAINSSTRING ( MAX ( 'Type'[Month_FY] ), "FY26" ), CALCULATE ( SUM ( 'Table'[FY26] ), 'Table'[Month] = a )
        ),
        IF (
            CONTAINSSTRING ( SELECTEDVALUE ( 'Type'[Month_FY] ), "Difference" ),
            SWITCH (
                TRUE (),
                CONTAINSSTRING ( MAX ( 'Type'[Month_FY] ), "FY24" ),
                    ABS (
                        CALCULATE (
                            SUM ( 'Table'[FY24] ),
                            'Table'[Month] IN VALUES ( 'Table A'[Month] )
                        )
                            - CALCULATE (
                                SUM ( 'Table'[FY24] ),
                                'Table'[Month] IN VALUES ( 'Table B'[Month] )
                            )
                    ),
                CONTAINSSTRING ( MAX ( 'Type'[Month_FY] ), "FY25" ),
                    ABS (
                        CALCULATE (
                            SUM ( 'Table'[FY25] ),
                            'Table'[Month] IN VALUES ( 'Table A'[Month] )
                        )
                            - CALCULATE (
                                SUM ( 'Table'[FY25] ),
                                'Table'[Month] IN VALUES ( 'Table B'[Month] )
                            )
                    ),
                CONTAINSSTRING ( MAX ( 'Type'[Month_FY] ), "FY26" ),
                    ABS (
                        CALCULATE (
                            SUM ( 'Table'[FY26] ),
                            'Table'[Month] IN VALUES ( 'Table A'[Month] )
                        )
                            - CALCULATE (
                                SUM ( 'Table'[FY26] ),
                                'Table'[Month] IN VALUES ( 'Table b'[Month] )
                            )
                    )
            )
        )
    )

Then put the month-type field in column  and and type field in row and put the measure to the value in the matrix visual.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnbasha33]

@Anonymous 
FYDifference =
VAR SelectedMonth = SELECTEDVALUE(MainTable[Selected Month])
RETURN
CALCULATE(
SUM('Table A'[Value]),
FILTER(
'Table A',
'Table A'[Month] = SelectedMonth
)
) -
CALCULATE(
SUM('Table B'[Value]),
FILTER(
'Table B',
'Table B'[Month] = SelectedMonth
)
)

[Anonymous]

Hi @johnbasha33 ,

Thanks for the reply,

Table A and B have  name only 

Table A = VALUES(TableName[Month)

Thanks,
w_D