Forum Discussion
Custom row level Total in Matrix
The client has requested a customization for the total calculations as follows:
- Total 1 should be the normal sum of the values.
- Total 2 should be calculated as Total 1 plus the sum of the values in the second row.
- Total 3 should be Total 2 plus the sum of the values in the third row.
- This pattern should continue, with each subsequent total being the previous total plus the sum of the values in the next row.
tarunbisht20001 , Seem like you want cumulative in subtotal , Assume the lowest level is product name and you are using a measure M1
Then
Switch(True() ,
isinscope(Table[Product Name]), [M1],
calculate([M1], filter(allselected(Table), Table[Level2] <= Max(Table2[Level 2]) ) )
)
You can consider window function for cumulative too
How to Switch Subtotal and Grand Total in Power BI | Power BI Tutorials| isinscope: https://youtu.be/smhIPw3OkKA
Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f
2 Replies
tarunbisht20001 , Seem like you want cumulative in subtotal , Assume the lowest level is product name and you are using a measure M1
Then
Switch(True() ,
isinscope(Table[Product Name]), [M1],
calculate([M1], filter(allselected(Table), Table[Level2] <= Max(Table2[Level 2]) ) )
)
You can consider window function for cumulative too
How to Switch Subtotal and Grand Total in Power BI | Power BI Tutorials| isinscope: https://youtu.be/smhIPw3OkKA
Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07fThis will involve a disconnected table and a lot of DAX. I've attached a sample pbix on how to achieve this. Alternatively, if your client is willing to spend, Inforiver has a custom viz that can custom aggregations as a row or column to a matrix. https://appsource.microsoft.com/en-us/product/power-bi-visuals/xviz.inforiver-premium?tab=overview
