cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
munk
Helper I
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
PaulDBrown
Super User
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 =
    ADDCOLUMNS (
        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 )

matrix table.jpgmodel.jpg

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:

Result.jpg

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!
Paul on Linkedin.






View solution in original post

3 REPLIES 3
PaulDBrown
Super User
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 =
    ADDCOLUMNS (
        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 )

matrix table.jpgmodel.jpg

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:

Result.jpg

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!
Paul on Linkedin.






PaulDBrown
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!
Paul on Linkedin.






Capture.JPGCapture1.JPG

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Dev Camp Session 30

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.

Power BI Summit Carousel 2

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!

Thank you 2022 Review

2022 Monthly Feature Releases

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