Forum Discussion
Multiple measures as hierarchy in matrix
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
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
5 Replies
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,
LydiaHi Anonymous
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...
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
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
