Forum Discussion
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.
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 )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
3 Replies
- PaulDBrownCommunity 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 )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
- PaulDBrownCommunity 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?
- AnonymousNot applicable