cancel
Showing results for
Did you mean:
Helper I

## Matrix Table - Multiple Total Rows

Hello, I was wondering if there was a way to have multiple total rows with different summaries.

[EXAMPLE DATA]

Grand Total:
% Parent Column Total:

As far as I know, the only way to do this is to put them side by side in a new column.

1 ACCEPTED SOLUTION
Super User

Heres is one way. (I created a dimension table for item)

Create a new independent table using the following code:

``````Matrix Table =
VAR _item =
DISTINCT ( fTable[Item] ),
"Order", RANK.EQ ( fTable[Item], fTable[Item], ASC )
)
VAR _Rows =
COUNT ( 'Item table'[Item] )
VAR _totals = {
( "Total", _Rows + 1 ),
( "Grand %", _Rows + 2 )
}
RETURN
UNION ( _item, _totals )
``````

Now you need to create the equivalent following measures for each Quantity value

``Sum Q1 = SUM(fTable[Quantity 1])``
``````Sum Q1 All Items =
CALCULATE([Sum Q1], ALLSELECTED('Item table'[Item]))``````

and finally for the matrix:

``````Quantity 1 =
VAR _itmes =
CALCULATE (
[Sum Q1],
TREATAS ( VALUES ( 'Matrix Table'[Item] ), 'Item table'[Item] )
)
VAR _total = [Sum Q1 All Items]
VAR _Grand =
DIVIDE (
[Sum Q1 All Items],
[Sum Q1 All Items] + [Sum Q2 All Items] + [Sum Q3 All Items]
)
VAR _rows =
COUNT ( 'Item table'[Item] )
RETURN
SWITCH (
SELECTEDVALUE ( 'Matrix Table'[Order] ),
_rows + 1, _total,
_rows + 2, FORMAT ( _Grand, "percent" ),
_itmes
)
``````

Add the field from the Matrix Table to the visual and the measures to get:

Sample PBIX file attached

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!

3 REPLIES 3
Super User

Heres is one way. (I created a dimension table for item)

Create a new independent table using the following code:

``````Matrix Table =
VAR _item =
DISTINCT ( fTable[Item] ),
"Order", RANK.EQ ( fTable[Item], fTable[Item], ASC )
)
VAR _Rows =
COUNT ( 'Item table'[Item] )
VAR _totals = {
( "Total", _Rows + 1 ),
( "Grand %", _Rows + 2 )
}
RETURN
UNION ( _item, _totals )
``````

Now you need to create the equivalent following measures for each Quantity value

``Sum Q1 = SUM(fTable[Quantity 1])``
``````Sum Q1 All Items =
CALCULATE([Sum Q1], ALLSELECTED('Item table'[Item]))``````

and finally for the matrix:

``````Quantity 1 =
VAR _itmes =
CALCULATE (
[Sum Q1],
TREATAS ( VALUES ( 'Matrix Table'[Item] ), 'Item table'[Item] )
)
VAR _total = [Sum Q1 All Items]
VAR _Grand =
DIVIDE (
[Sum Q1 All Items],
[Sum Q1 All Items] + [Sum Q2 All Items] + [Sum Q3 All Items]
)
VAR _rows =
COUNT ( 'Item table'[Item] )
RETURN
SWITCH (
SELECTEDVALUE ( 'Matrix Table'[Order] ),
_rows + 1, _total,
_rows + 2, FORMAT ( _Grand, "percent" ),
_itmes
)
``````

Add the field from the Matrix Table to the visual and the measures to get:

Sample PBIX file attached

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Super User

It depends on how you build the matrix. You can have the measures as rows for example. Can you provide a more detailed depiction?

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Helper I

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Ted's Dev Camp - January 26, 2023

This session will provide guidance and teach campers the skills required to build Power BI reports that support multiple languages.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors