Display Attribute - Hierarchy along with Measure name in Cross table row(separate columns)

[Zaeem7]

Can someone suggest how can i display Product name and Measure name in rows(separate columns) of a Matrix chart.

Below is my dataset:

Category	Brand	Month	Sales	Gross Profit	Operating Profit
A	A1	Feb	200	120	90
B	B1	Jan	100	60	40
A	A2	Mar	300	170	120
B	B2	Feb	300	80	50

I am giving the user flexibity to dynamically select whichever measures they want to see in the Matrix table and for that i have created a seperate table containing only Measure names and this table is used in slicer. I have kept Category - Brand Hierarchy in row, Month - Hierarcy in column and my seperate table in values.

How can i create a Matrix chart similar to below:

Category	Brand	Values	Jan	Feb	Mar
A	A1	Sum of Sales		200
A	A1	Sum of Gross Profit		120
A	A1	Sum of Operating Profit		90
A	A2	Sum of Sales			300
A	A2	Sum of Gross Profit			170
A	A2	Sum of Operating Profit			120
B	B1	Sum of Sales	100
B	B1	Sum of Gross Profit	60
B	B1	Sum of Operating Profit	40
B	B2	Sum of Sales		300
B	B2	Sum of Gross Profit		80
B	B2	Sum of Operating Profit		50

I want to give the user flexibity to drill through Category -hierarchy and Time - Hierarchy to see data at multiple levels. 

[quentin_vigne]

Hi @Zaeem7

I don't know if you already did the switch measure but here is for you :

After creating your table with measure name in the column, create this measure : 

Selection = IF(HASONEVALUE(Table2[Values]);SWITCH(VALUES(Table2[Values]);"Sum of Sales";SUM(Table1[Sales]);"Sum of Gross Profit";SUM(Table1[Gross Profit]);"Sum of Operating Profit";SUM(Table1[Operating Profit]);sum(Table1[Sales])))

Then create your slicer with the table that contain your measure name.

And put your Selection measure in your table.

It doesn't look 100% exactly the same but it works. Hope this help enough

- Quentin

[Zaeem7]

Hi @quentin_vigne

Thanks for the reply.

Yes, i have done the steps you have mentioned below and i am able to dynamically select Measures from the slicer in the table.. But the issue is i can't see the Measure name unless i drill down to the lowest level.

The help needed here is to be able to see Measure names in seperate column and Category - Hierarchy in another. I mean i want to exactly replicate the below table in Power BI Matrix Chart.

Category	Brand	Values	Jan	Feb	Mar
A	A1	Sum of Sales		200
A	A1	Sum of Gross Profit		120
A	A1	Sum of Operating Profit		90
A	A2	Sum of Sales			300
A	A2	Sum of Gross Profit			170
A	A2	Sum of Operating Profit			120
B	B1	Sum of Sales	100
B	B1	Sum of Gross Profit	60
B	B1	Sum of Operating Profit	40
B	B2	Sum of Sales		300
B	B2	Sum of Gross Profit		80
B	B2	Sum of Operating Profit		50

Also, do you know how can we apply different conditional formatting to different Measures in above method?

[v-jiascu-msft · Accepted solution]

Hi @Zaeem7,

The hierarchy is collapsed in the Matrix by default. So we need to extend to next level to see more details. You can disable the option in the snapshot to make them all show in columns.

Best Regards,

Dale

[Zaeem7]

Understood. Thanks a lot!