Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
Anonymous
Not applicable

row grouping to the sub total of the matrix

Hi,

 

In power bi matrix can we have row grouping to the sub totals as mentioned in attached table.


 Untitled.png

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.

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

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] )
)

1.png

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"
)

2.png

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'
)

3.png

Finally, drag “Country, Name, Year” to Rows field, drag “Exp” to Values field

4.png

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

 

View solution in original post

1 REPLY 1
v-juanli-msft
Community Support
Community Support

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] )
)

1.png

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"
)

2.png

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'
)

3.png

Finally, drag “Country, Name, Year” to Rows field, drag “Exp” to Values field

4.png

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

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!