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

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.

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.

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