Forum Discussion

srinivas_fabric's avatar
srinivas_fabric
Regular Visitor
1 year ago
Solved

Matrix Visual: How to Expand Q1 into Jan, Feb, Mar + Q1 Total Using +/- Drill Option

Hello Power BI Experts, I'm using Power BI Version: 2.143.1204.0 64-bit (May, 2025). I'm working with a Matrix visual and want to implement a feature similar to Excel pivot tables: I want the Q1 co...
Need Help
Tips and Tricks
  • v-priyankata's avatar
    v-priyankata
    1 year ago

    Hi srinivas_fabric 

    Thank you for reaching out to the Microsoft Fabric Forum Community.

     

    Please find attached pbix file, it may help you.
    If this information is helpful, please “Accept as solution” to assist other community members in resolving similar issues more efficiently.
    Thank you.

grognard's avatar
grognard
Helper I
1 year ago

Hey srinivas_fabric,

It looks like what you want to use is hierarchies in Power BI which allows you to drilldown on a single value, what I did here was use the “unpivot” function in Power Query to convert the month columns into a single column, then turned that into a date hirearchy: 

 

Hierarchies work best with single numeric column, and multiple different categories. Unfortunately if we compare the values provided with the results of using “Sum”, “Average” and “Median” against the values provided for Q1, Q2, Q3 and Q4, we can see there's a significant difference for each number (Demonstrated in the "Comparison" page of this pbix file):  


ScoreCard.pbix

 

If the values given in Q1, Q2, Q3, and Q4 can’t be determined by the “sub-level” in the hierarchy, then the hierarchy won’t work very well, and it might be worth considering another way to visualize the data. 

 

My reccomendation would be to figure out why the Q1, Q2, Q3, Q4 values aren't actually an aggregate value, then unpivot the table, and use Power BI's built in date hirearchy functionality.