row grouping to the sub total of the matrix

[Anonymous]

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.

[v-juanli-msft · Accepted 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