Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Community Champion
Community Champion

@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
Community Champion
Community Champion

@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!



Anonymous
Not applicable

Thank you!!!!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.