Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Row Level Running Total on Matrix Groups

Hello Community Comrades,

           I desperately wanted to get a solution to find out a Running Total for the Matrix Group in Power BI. I am pulling data from a single column and creating Major Groups and Group to calculate the running total like the one below, pls ref the screenshots for your understanding,

 

PBIX file

 

Excel Source file

 

Could anyone please help on this? 

 

 

Sum of Group A - Sum of Group B = Total of Account

Total of Account - Sum of Group C = Total of Account 2 

Total of Account 2 + Sum of Group D = Total of Account 3

 

 

Many Thanks,

Praveen. 

Need Help
Show and Tell
Tips and Tricks

1 Reply

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    Hi praveenkumarv,

     

    There's no such option in matrix which can aggregate total values. In your senario, you can create three measures as below to achieve your requirement:

    Total of Account = CALCULATE(SUM('Single Column Data'[Amount]), FILTER(ALL('Single Column Data'), 'Single Column Data'[Account Balance sheet (groups)] = "Group A" && 'Single Column Data'[Account Balance sheet (Major groups)] = "Account")) - CALCULATE(SUM('Single Column Data'[Amount]), FILTER(ALL('Single Column Data'), 'Single Column Data'[Account Balance sheet (groups)] = "Group B" && 'Single Column Data'[Account Balance sheet (Major groups)] = "Account"))

Total of Account 2 = [Total of Account] - CALCULATE(SUM('Single Column Data'[Amount]), FILTER(ALL('Single Column Data'), 'Single Column Data'[Account Balance sheet (groups)] = "Group C" && 'Single Column Data'[Account Balance sheet (Major groups)] = "Account 2"))

Total of Account 3 = [Total of Account 2] + CALCULATE(SUM('Single Column Data'[Amount]), FILTER(ALL('Single Column Data'), 'Single Column Data'[Account Balance sheet (groups)] = "Group D" && 'Single Column Data'[Account Balance sheet (Major groups)] = "Account 3"))

     

     

    Regards,

    Jimmy Tao