How to show the hierarchical subtotals on the left in the matrix in power bi?

[Anonymous]

I have a table with sales data.

Year	Month	Sales
2000	Jan	100
2000	Feb	200
2000	March	300
2001	Jan	500
2001	Feb	100
2001	March	200
2002	Jan	600
2002	Feb	150
2002	March	300

I using matrix in power bi, I want to see the sub totals for the years on the left:

Expected result:

Total for 2000	Jan	Feb	March	Total for 2001	Jan	Feb	March	Total for 2002	Jan	Feb	March
600	100	200	300	800	500	100	200	1050	600	150	300

I tried following dax but I am not getting the result:

Total Value = SWITCH(

                SELECTEDVALUE(Date_Sort_Order[Month]),

                "Total",SUM(Table1[Sales]),

                CALCULATE(SUM(Table1[Sales]),

                Table1[Month] = SELECTEDVALUE(Date_Sort_Order[Month])))

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

According to your description, you want to show each year subtotal in Matrix visual.

Here are the steps you can refer to , my test data is the same as yours.

(1)We need to create a dimension table in Power Query Editor, we can create a blank query in Power Query Editor and put this M code in the "Advanced Editor":

let
    Source = Table.Group(Table,"Year",{{"test",(x)=>  List.InsertRange(List.Transform(x[Month],(y)=>y &"("& Text.From( x[Year]{0})&")"   )  ,0,{  "Total for " & Text.From( x[Year]{0})   })    }  ,{"test2",(x)=> List.InsertRange(x[Month],0,{"Total"})      } }     ),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.FromColumns({[test],[test2]})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"test", "test2"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1", "Column2"}, {"Column1", "Column2"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded Custom", "Index", 1, 1, Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Column1", "Month"}, {"Column2", "Flag"}})
in
    #"Renamed Columns"

This is the table we need to put on the Matrix's column headers:

(2)Then we can create a measure in Desktop:

Measure = var _cur_year  = MAX('Query1'[Year])
var _month =  MAX('Query1'[Flag])
var _total  = CALCULATE(SUM('Table'[Sales]) , 'Table'[Year]=_cur_year)
var _month_value = CALCULATE( SUM('Table'[Sales]) , 'Table'[Year]=_cur_year ,'Table'[Month] = _month)
return
IF(_month="Total" , _total , _month_value)

Then we can put this fields on the visual and we can get the result as follows:

We need to add the "(Year)" in the Month field for the column headers due to the duplicate value will not show in the column headers.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

I was able to solve the following issue: How would the syntax for the Power Query change if the month is numeric rather than a text?

In the current report, the annual total does not change if I select month Jan and Feb.

Total for 2000	Jan	Feb	Total for 2001	Jan	Feb	Total for 2002	Jan	Feb
300	100	200	600	500	100	750	600	150

[v-yueyunzh-msft]

HI , @Anonymous 

Thanks for your quick response ! And it is glad to hear that you have solve the first issue by yourself! And now the issue is that "the annual total does not change if I select month Jan and Feb".

We can use the [FLag] as the slicer . And then we can update the measure to this:

Measure = var _cur_year  = MAX('Query1'[Year])
var _month =  MAX('Query1'[Flag])
var _months =  ALLSELECTED('Query1'[Flag])
var _total  = CALCULATE(SUM('Table'[Sales]) , 'Table'[Year]=_cur_year , FILTER('Table','Table'[Month] in  _months)   )
var _month_value = CALCULATE( SUM('Table'[Sales]) , 'Table'[Year]=_cur_year ,'Table'[Month] = _month)
return
IF(_month="Total" , _total , _month_value)

Then we can make the annual total changed based on the slicer :

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

This works nicely. How would the syntax for the Power Query change if the month is numeric rather than a text?

[Ritaf1983]

Hi @Anonymous 
Unfortunately, we don't have the option to "move" subtotals to the first column.
Please refer to the linked discussion and tutorial of some workaround as an alternative:

https://stackoverflow.com/questions/65301066/how-to-get-matrix-subtotal-to-the-first-column-in-power-bi

https://www.youtube.com/watch?v=VRUs0pWgsdE

you also can suggest the issue as the idea here :

https://ideas.fabric.microsoft.com/

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly