Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
In power bi matrix can we have row grouping to the sub totals as mentioned in attached table.
I dont want Year to be in column of matrix, our client needs that vertically. As of now Am getting total as single row, which is combination of 2016 & 2017.
Solved! Go to Solution.
Hi @Anonymous
In power bi matrix, it doesn’t support list sub-total with two rows like this.
Here is a workaround by create a new table.
First, create a calculated column,
Total = CALCULATE ( SUM ( Sheet1[Expenditure] ), ALLEXCEPT ( Sheet1, Sheet1[Year], Sheet1[Country] ) )
Second, create a new table
New Table_1 = ADDCOLUMNS ( SUMMARIZE ( Sheet1, Sheet1[Country], Sheet1[Year], "Total per country per year", AVERAGE ( Sheet1[Total] ) ), "Name", "ZTotal" )
Next, join the original table and the new table
New table_2 = UNION ( SELECTCOLUMNS ( Sheet1, "Country", Sheet1[Country], "Year", Sheet1[Year], "Exp", Sheet1[Expenditure], "Name", Sheet1[Name] ), 'New Table_1' )
Finally, drag “Country, Name, Year” to Rows field, drag “Exp” to Values field
Noted:
The sub-total will not change with the slicer selected;
To make the “Name” column sorted as we wanted, we name it with “ZTotal” instead of “Total”.
Best Regards
Maggie
Hi @Anonymous
In power bi matrix, it doesn’t support list sub-total with two rows like this.
Here is a workaround by create a new table.
First, create a calculated column,
Total = CALCULATE ( SUM ( Sheet1[Expenditure] ), ALLEXCEPT ( Sheet1, Sheet1[Year], Sheet1[Country] ) )
Second, create a new table
New Table_1 = ADDCOLUMNS ( SUMMARIZE ( Sheet1, Sheet1[Country], Sheet1[Year], "Total per country per year", AVERAGE ( Sheet1[Total] ) ), "Name", "ZTotal" )
Next, join the original table and the new table
New table_2 = UNION ( SELECTCOLUMNS ( Sheet1, "Country", Sheet1[Country], "Year", Sheet1[Year], "Exp", Sheet1[Expenditure], "Name", Sheet1[Name] ), 'New Table_1' )
Finally, drag “Country, Name, Year” to Rows field, drag “Exp” to Values field
Noted:
The sub-total will not change with the slicer selected;
To make the “Name” column sorted as we wanted, we name it with “ZTotal” instead of “Total”.
Best Regards
Maggie