cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Problem with creating running total with multiple categories

Hi, 

I have looked at many different community posts but I haven't found one that's solution works with my problem.

I am trying to create a running total for a dataset that contains multiple people, with 2 different types of accounts.  Most of the time people will have entries for both accounts, however they may sometimes be missing entires for one or both of the types for a month.  I have tried solutions in both DAX and Power Query and I have no further ideas.

 

Here is an example of my data:

PersonMonthAmountType
Jim10Receivables
Jim2350Receivables
Jim3-650Prepaid Rent
Jim3275Receivables
Jim4-5Prepaid Rent
Jim40Receivables
Jim5-5Prepaid Rent
Jim50Receivables
Jim6-5Prepaid Rent
Jim60Receivables
Jim7-5Prepaid Rent
Jim70Receivables
Jim8-5Prepaid Rent
Jim80Receivables
Jim9-5Prepaid Rent
Jim90Receivables
Jim10700Prepaid Rent
Jim10-300Receivables
Jim11-1000Receivables
Fred1770Receivables
Fred25000Receivables
Fred3-7000Receivables
Fred4-70Receivables
Fred5-70Receivables
Fred6-70Receivables
Fred7-80Receivables
Fred8-60Receivables
Fred9-90Receivables
Fred10-150Receivables
Fred1140Receivables

 

Here is what I want my output to be:

PersonMonthAmountTypeRunning Total
Jim10Receivables0
Jim2350Receivables350
Jim3-650Prepaid Rent-650
Jim3275Receivables625
Jim4-5Prepaid Rent-645
Jim40Receivables625
Jim5-5Prepaid Rent-640
Jim50Receivables625
Jim6-5Prepaid Rent-635
Jim60Receivables625
Jim7-5Prepaid Rent-630
Jim70Receivables625
Jim8-5Prepaid Rent-625
Jim80Receivables625
Jim9-5Prepaid Rent-620
Jim90Receivables625
Jim10700Prepaid Rent80
Jim10-300Receivables325
Jim11-1000Receivables-675
Fred1770Receivables770
Fred25000Receivables5770
Fred3-7000Receivables-1230
Fred4-70Receivables-1300
Fred5-70Receivables-1370
Fred6-70Receivables-1440
Fred7-80Receivables-1520
Fred8-60Receivables-1580
Fred9-90Receivables-1670
Fred10-150Receivables-1820
Fred1140Receivables-1780

 

Each account for each person should have their own running total.  So Jim would have one running total for Recievables and another for Prepaid Rent.  Fred would have his own unique running totals.  There are hundreds of thousands of entries in the real dataset.

 

Any help would be much appreciated!

1 ACCEPTED SOLUTION
camargos88
Super User III
Super User III

@Anonymous ,

 

Try this measure:

 

_Running Total = CALCULATE(SUM('Table'[Amount]), FILTER(ALL('Table'[Month]), 'Table'[Month] <= MAX('Table'[Month])))

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

2 REPLIES 2
camargos88
Super User III
Super User III

@Anonymous ,

 

Try this measure:

 

_Running Total = CALCULATE(SUM('Table'[Amount]), FILTER(ALL('Table'[Month]), 'Table'[Month] <= MAX('Table'[Month])))

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

Anonymous
Not applicable

Thank you!!!!!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors