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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.