Adding comparative rows at the bottom of Matrix Visual

[rashidanwar]

Hello Community,

I have a following Matrix Visual in Power BI, and I want to add two rows at bottom of it. It should be the difference between 2022/2023 and 2023/2024.

Year	NOV	DEC	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT
2021/2022	10	12	14	15	16	17	18	19	20	15	16	19
2022/2023	11	13	15	21	22	19	11	16	18	15	19	20
2023/2024	8	14	19	22	24	18	14	10	20	18	23	22

After adding the two rows the Visual Matrix should look like as follows.

Year	NOV	DEC	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT
2021/2022	10	12	14	15	16	17	18	19	20	15	16	19
2022/2023	11	13	15	21	22	19	11	16	18	15	19	20
2023/2024	8	14	19	22	24	18	14	10	20	18	23	22
Diff from last year	-3	1	4	1	2	-1	3	-6	2	3	4	2
% Diff	-27%	8%	27%	5%	9%	-5%	27%	-38%	11%	20%	21%	10%

I'll appreciate any help in this regard.

Thank you!
Rashid

[v-zhouwen-msft · Accepted solution]

Hi @rashidanwar ,

Please follow these steps:

1.Use Power Query to create a blank query and perform an "append" operation

let
    Source = Table,
    selectColumn = Table.SelectColumns(Source,{"Month"}),
    distinct = Table.Distinct(selectColumn),
    #"Added Custom" = Table.AddColumn(distinct, "Year", each "Diff from last year")
in
    #"Added Custom"

2.Use the following DAX expression to create a measure

MEASURE =
VAR _row =
    SELECTEDVALUE ( Append1[Year] )
VAR _column =
    SELECTEDVALUE ( Append1[Month] )
VAR _difference =
    CALCULATE ( SUM ( Append1[Value] ), 'Append1'[Year] = "2023/2024" )
        - CALCULATE ( SUM ( Append1[Value] ), 'Append1'[Year] = "2022/2023" )
VAR _result =
    IF (
        _row = "Diff from last year",
        FORMAT ( _difference, "0" ),
        IF (
            ISBLANK ( _row ),
            FORMAT (
                DIVIDE (
                    _difference,
                    CALCULATE ( SUM ( Append1[Value] ), 'Append1'[Year] = "2022/2023" )
                ),
                "0%"
            ),
            FORMAT ( SUM ( 'Append1'[Value] ), "0" )
        )
    )
RETURN
    _result

3.Final output

Best Regards,
Wenbin Zhou

[rashidanwar]

Thank you so much @sanalytics. This is a good solution, indeed!

[sanalytics · Accepted solution]

@rashidanwar 

I know this is late.  But you can have a short look on my solution as well.
Assuming your dataset look like below

then follow below steps

Step 1 : 
create a simple disconnected calculated table by DAX .

ResultTable = 
VAR _1 = 
ROW( "Year","Diff from last Year" )
VAR _2 =
ROW( "Year","Diff%" )
VAR _CombTable = 
UNION(
	VALUES( Source[Year] ),
	_1,_2
)
VAR _Result = 
ADDCOLUMNS(
    _CombTable, "MaxYear",MAX( Source[Year] )
)

RETURN
_Result

Step 2 : Create a consolidated DAX for showing value. Below code. (Assuming you have DAX code for Diff from Last year and Diff %)

Show Value = 
VAR _ValueBase = 
CALCULATE(
    SUM( Source[Value] ),
    TREATAS(
        VALUES( 'ResultTable'[Year] ), Source[Year] 
    ) )
VAR _Result = 
IF(
    MAX( ResultTable[Year] ) = "Diff from last Year",
      CALCULATE(
    [Diff from Last Year],
    TREATAS(
        VALUES( 'ResultTable'[MaxYear] )
        ,Source[Year]
    )
) ,
IF(
    MAX( ResultTable[Year] ) = "Diff%",
       CALCULATE(
    [Diff %],
    TREATAS(
        VALUES( 'ResultTable'[MaxYear] )
        ,Source[Year]
    )
),
_ValueBase
) )
RETURN
_Result

Step 3 : For formatting, go ti Dynamic formatting  and paste below DAX 

IF(
    MAX( ResultTable[Year] ) = "Diff%",
    "0%","#,##0"
)

Below screenshot

 you can find the pbix file in below link

https://we.tl/t-ll7HvM8Gz7

Hope this will help

Regards,

sanalytics

[rashidanwar]

@v-zhouwen-msft @Ritaf1983 thank you so much.

[v-zhouwen-msft · Accepted solution]

Hi @rashidanwar ,

Please follow these steps:

1.Use Power Query to create a blank query and perform an "append" operation

let
    Source = Table,
    selectColumn = Table.SelectColumns(Source,{"Month"}),
    distinct = Table.Distinct(selectColumn),
    #"Added Custom" = Table.AddColumn(distinct, "Year", each "Diff from last year")
in
    #"Added Custom"

2.Use the following DAX expression to create a measure

MEASURE =
VAR _row =
    SELECTEDVALUE ( Append1[Year] )
VAR _column =
    SELECTEDVALUE ( Append1[Month] )
VAR _difference =
    CALCULATE ( SUM ( Append1[Value] ), 'Append1'[Year] = "2023/2024" )
        - CALCULATE ( SUM ( Append1[Value] ), 'Append1'[Year] = "2022/2023" )
VAR _result =
    IF (
        _row = "Diff from last year",
        FORMAT ( _difference, "0" ),
        IF (
            ISBLANK ( _row ),
            FORMAT (
                DIVIDE (
                    _difference,
                    CALCULATE ( SUM ( Append1[Value] ), 'Append1'[Year] = "2022/2023" )
                ),
                "0%"
            ),
            FORMAT ( SUM ( 'Append1'[Value] ), "0" )
        )
    )
RETURN
    _result

3.Final output

Best Regards,
Wenbin Zhou

[Ritaf1983]

Hi @rashidanwar 
You can achieve it with the calculated groups.

Please refer to the linked video :

https://www.youtube.com/watch?v=gNLIaBMXr40

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