Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

DAX - Matrix Tale Difference - Selected Value (Slicer)

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

3 Replies

  • johnbasha33's avatar
    johnbasha33
    Super User
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi johnbasha33 ,

      Thanks for the reply,

      Table A and B have  name only 

      Table A = VALUES(TableName[Month)


      Thanks,
      w_D

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.