Hide certain parts of matrix

[Anonymous]

Good afternoon.

I have the following matrix

 But , i want in the following form:

Type	2019	2020	2021	Chg ABS	Chg %
A-Revenue	16,352,233	19,912,526	22,275,253	2,362,727	11.9%
B-Cost of Sales	-697,902	-931,800	-1,637,882	-706,082	75.8%
C-GP	15,654,331	18,980,726	20,637,371	1,656,645	8.7%
D-Does	-12,123,759	-14,666,690	-15,194,498	-527,808	3.6%
E-OP	3,530,572	4,314,036	5,442,873	1,128,837	26.2%

It's possible?

Also, I don't understand how to make the matrix calculate the Subtotal correctly. The Chg ABS=22,275,253-19,912,526 and
Chg %= 2,362,727/19,912,526

[v-luwang-msft · Accepted solution]

Hi  @Anonymous ,

Test the following measure:

Chg_ABS = 
CALCULATE (
    MAX ( 'Table'[value] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Type] = MAX ( 'Table'[Type] )
            && 'Table'[Year] = MAX ( 'Table'[Year] )
    )
)
    - CALCULATE (
        MAX ( 'Table'[value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Type] = MAX ( 'Table'[Type] )
                && 'Table'[Year]
                    = MAX ( 'Table'[Year] ) - 1
        )
    )

Chg % = FORMAT(DIVIDE([Chg_ABS],CALCULATE(MAX('Table'[value]),FILTER(ALL('Table'),'Table'[Type]=MAX('Table'[Type])&&'Table'[Year]=MAX('Table'[Year])))),"0.001")*100&""&"%"

And if you want to show as your show ,test below:

Step1,create the following column:

Chg_ABS2 = IF('Table'[Year]=2021,
CALCULATE (
    MAX ( 'Table'[value] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Type] = EARLIER( 'Table'[Type] )
            && 'Table'[Year] = EARLIER ( 'Table'[Year] )
    )
)
    - CALCULATE (
        MAX ( 'Table'[value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Type] = EARLIER ( 'Table'[Type] )
                && 'Table'[Year]
                    = EARLIER ( 'Table'[Year] ) - 1
        )
    ),BLANK())

Chg %2 = IF('Table'[Year]=2021,FORMAT(DIVIDE([Chg_ABS],CALCULATE(MAX('Table'[value]),FILTER(ALL('Table'),'Table'[Type]=EARLIER('Table'[Type])&&'Table'[Year]=EARLIER('Table'[Year])))),"0.001")*100&""&"%",BLANK())

Then create the following talbe:

Table3 = ADDCOLUMNS(SUMMARIZE('Table','Table'[Type],'Table'[Chg_ABS2]),"Year","Chg_ABS")

Table4 = ADDCOLUMNS(SUMMARIZE('Table','Table'[Type],'Table'[Chg %2]),"Year","Chg %")

merge table:

Table2 = UNION( SUMMARIZE('Table','Table'[Year],'Table'[Type],'Table'[value]), SELECTCOLUMNS(Table3,"Year",Table3[Year],"Type",Table3[Type],"value",Table3[Chg_ABS2]), SELECTCOLUMNS(Table4,"Year",Table4[Year],"Type",Table4[Type],"value",Table4[Chg %2]))

Create table base on the table2:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[Anonymous]

Hi, v-luwang-msft , thanks for your deeply appreciated help. It worked with some minor modifications.

[Greg_Deckler]

@Anonymous Yes it is possible: The New Hotness (Custom Matrix Hierarchy) - Microsoft Power BI Community