cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Hide certain parts of matrix

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

1 ACCEPTED SOLUTION
Community Support

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:

Best Regards

Lucien

3 REPLIES 3
Community Support

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:

Best Regards

Lucien

Anonymous
Not applicable

Hi,  , thanks for your deeply appreciated help. It worked with some minor modifications.

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...