Multiple measures as hierarchy in matrix

[satubuku83]

Hi All,

I am new (1 month exp) in using Power BI. I am having some difficulties in designing the matrix in Power BI. Would greatly appreciate if you could share any solutions to it.

Design of Fact table:

Policy ID	Year	Franchiser Indicator	Car Make	Earned Premium	Earned Count	Average Earned Premium
1	2016	Franchiser	Perodua	40000	100	400
2	2016	Non Franchiser	Perodua	20000	40	500
3	2016	Franchiser	Honda	30000	80	375
4	2016	Non Franchiser	Honda	15000	30	500
5	2017	Franchiser	Perodua	35000	80	437.5
6	2017	Non Franchiser	Perodua	25000	20	1250
7	2017	Franchiser	Honda	25000	25	1000
8	2017	Non Franchiser	Honda	10000	20	500

I would like to have output in Power Bi as Matrix in following format (assuming metrics values are in center of header):

Metrics	Earned Premium						Earned Count						Average Earned Premium
Year	2016			2017			2016			2017			2016			2017
Make/Franchiser Ind	Franchiser	Non Franchiser	Total	Franchiser	Non Franchiser	Total	Franchiser	Non Franchiser	Total	Franchiser	Non Franchiser	Total	Franchiser	Non Franchiser	Total	Franchiser	Non Franchiser	Total
Perodua	40000	20000	60000	35000	25000	60000	100	40	140	80	20	100	400	500	900	437.5	1250	1687.5
Honda	30000	15000	45000	25000	10000	35000	80	30	110	25	20	45	375	500	875	1000	500	1500

Is there any way to do this in Power BI? Or the only was is to create another table by denormalizing the fact table then only i can do in such format?

Regards

CS

[v-yuezhe-msft · Accepted solution]

@satubuku83,

Do you create calculated measures called Earned Premium,Earned Count, Average Earned Premium in your table? Or do you have built-in fields(or calculated columns) called Earned Premium,Earned Count, Average Earned Premium in the table?

If you the mentioned fields are calculated measures, you would need to create a disconnected table as described in this similar blog. If the mentioned fields are built-in, you can create a Matrix visual and drill down it using the highlighted button as shown in the following screenshot.

Regards,
Lydia

[satubuku83]

Hi @v-yuezhe-msft

Thanks for reply. Really appreciate.

I am using dax measures for the calculation.

And most importantly, measure needs to show first, then only break down by franchise and non franchise.

Seemed like there is no easier way but to design it in normalized form...

[v-yuezhe-msft]

@satubuku83,

Have your created DAX as shown in the blog below?

https://exceleratorbi.com.au/measures-on-rows-here-is-how-i-did-it/

Regards,
Lydia

[satubuku83]

@v-yuezhe-msft , Many thanks for the solution.

I took quite a long time to understand it. Interesting way instead of denormalizing.

Appreciate the advice!

[satubuku83]

Hi All,

I am new to Power BI (1 month exp).

I have the following fact table :

Policy ID	Year	Franchiser Indicator	Car Make	Earned Premium	Earned Count	Average Earned Premium
1	2016	Franchiser	Perodua	40000	100	400
2	2016	Non Franchiser	Perodua	20000	40	500
3	2016	Franchiser	Honda	30000	80	375
4	2016	Non Franchiser	Honda	15000	30	500
5	2017	Franchiser	Perodua	35000	80	437.5
6	2017	Non Franchiser	Perodua	25000	20	1250
7	2017	Franchiser	Honda	25000	25	1000
8	2017	Non Franchiser	Honda	10000	20	500

And would like to output as following as a matrix in Power BI

Metrics	Earned Premium						Earned Count						Average Earned Premium
Year	2016			2017			2016			2017			2016			2017
Make/Franchiser Ind	Franchiser	Non Franchiser	Total	Franchiser	Non Franchiser	Total	Franchiser	Non Franchiser	Total	Franchiser	Non Franchiser	Total	Franchiser	Non Franchiser	Total	Franchiser	Non Franchiser	Total
Perodua	40000	20000	60000	35000	25000	60000	100	40	140	80	20	100	400	500	900	437.5	1250	1687.5
Honda	30000	15000	45000	25000	10000	35000	80	30	110	25	20	45	375	500	875	1000	500	1500

Is there any way to do this using the fact table design in Power BI or the only way to display as such is by denormalizing the fact in Power BI?

Regards

CS