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

[srinivas_fabric]

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 column to show a “+” icon beside it.

On clicking the “+”, it should expand to show Jan, Feb, Mar, and Q1 subtotal.

When collapsed, I only want to see Q1 (with similar setup for Q2, Q3, Q4).

I've added Year > Quarter > Month in the column hierarchy and enabled the +/- icons in the format pane, but I'm not sure how to show Q1 alone by default and allow expansion on demand.

Is there a way to make this work with proper subtotals and clean drill experience?

Appreciate your guidance or any video links!

Please download the Power BI file from here:

https://drive.google.com/file/d/1tHpKQPDUUxJ7lLged_wN6Le6lZL2IBIm/view?usp=sharing

[v-priyankata]

Hi @srinivas_fabric 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you

[srinivas_fabric]

Thank you foryour response,Please send the sample fie for better understandingto me.

[v-priyankata · Accepted solution]

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.

[v-priyankata]

Hi @srinivas_fabric 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

[v-priyankata]

Hi @srinivas_fabric 

Hope everything’s going smoothly on your end. We haven’t heard back from you, so I wanted to check if the issue got sorted. If yes, marking the solution would be awesome for others who might run into the same thing.

[Ashish_Excel]

Hi,

Run this M code in Power Query to transform your data into a proper dataset

let
    Source = Csv.Document(File.Contents("C:\Users\Ashish Mathur\Desktop\Updated_KPI_Dashboard.csv"),[Delimiter=",", Columns=21, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Category", "KPI's Description", "2024 Actual", "2025 Target"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Attribute] <> "Q1" and [Attribute] <> "Q2" and [Attribute] <> "Q3" and [Attribute] <> "Q4" and not Text.Contains([Attribute], "YTD",Comparer.OrdinalIgnoreCase))),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Year", each List.Transform(List.Select(Table.ColumnNames(#"Filtered Rows"), each Text.Contains(_,"Actual")),each Text.Remove(_,{"A".."Z","a".."z"}))),
    #"Expanded Year" = Table.ExpandListColumn(#"Added Custom", "Year"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Year", "Date", each "1/"&[Attribute]&"/"&[Year], type date),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute", "Year"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Category", type text}, {"KPI's Description", type text}, {"2024 Actual", type text}, {"2025 Target", type text}, {"Value", type number}, {"Date", type date}})
in
    #"Changed Type"

The revised data layout will be

Now create a Calendar table and write your measures.

Hope this helps.

[srinivas_fabric]

Thank ypu for your response,Please send me the sample file.

[grognard]

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.