Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
147 | |
85 | |
66 | |
52 | |
46 |
User | Count |
---|---|
215 | |
90 | |
83 | |
66 | |
58 |