Custom row level Total in Matrix

[tarunbisht20001]

The client has requested a customization for the total calculations as follows:

1. Total 1 should be the normal sum of the values.
2. Total 2 should be calculated as Total 1 plus the sum of the values in the second row.
3. Total 3 should be Total 2 plus the sum of the values in the third row.
4. This pattern should continue, with each subsequent total being the previous total plus the sum of the values in the next row.

[danextian]

Hi @tarunbisht20001 

This 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 

[amitchandak · Accepted solution]

@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