Create a Running Total

[Anonymous]

I'm having trouble creating a running total.  An example of my data looks like the attached.

 

I'm wanting to take the counts of the Label column and take a running total of those counts to create a table like this.

Label	Claim_Type	Count	Running Total
Need Completed Reimbursement Request Form	Manual	6	6
Statement/EOB Required	Online	4	10
Service after end of Coverage Period	Online	2	12
Balance Forward	Online	1	13
Other	Online	1	14
Payment/Proof of Orthodontia	Online	1	15
EOB Required	Manual	1	16
Letter of Medical Necessity	Online	1	17
No Receipt	Online	1	18
Credit Card Receipts	Online	1	19
Estimated Statement/EOB	Manual	1	20

[v-yuta-msft]

Hi ebrazis,

You should duplicate your original table then remove duplicate rows in the new table. Then create calculate column with DAX formula:

Count = CALCULATE(MAX(Table1[Count]))

Rank = RANKX(ALL('Table1 (2)'), 'Table1 (2)'[Count], , ASC, Dense)

Then you can create a running total calculate column like this:

Running Total = CALCULATE(SUMX('Table1 (2)', 'Table1 (2)'[Count]), FILTER('Table1 (2)', 'Table1 (2)'[Rank] >= EARLIER('Table1 (2)'[Rank])))

 

Regards,

Jimmy Tao

[Anonymous]

Hi Jimmy (@v-yuta-msft) - This solution almost got me what I trying to do.  The only thing that needs to be corrected is the Running Total is showing as 20 for all of those with a value of 1 when it should be showing at 13, 14, 15, 16, 17, 18, 19, 20 as it makes its way through each Label with a count of 1.  Is there a way to modify this in the current Running Total formula to do this?  Also, is there a way to modify the Rank so that the highest count shows as 1 and increases as the count decreases?  I played around with these a little bit and couldn't figure out either.

[parry2k · Accepted solution]

@AnonymousI hope this will do it. Please check and let us know.

[Greg_Deckler]

For running totals to work you generally need some kind of numeric value or date that you can compare. I would suggest another table that assigns a number to each of your categories that could serve this purpose.

[Anonymous]

Unfortunately we don't have any dates within the data.  I added a colimn which assigns a 1 for each line item, will this do the job?  If it does, how do I go about creating the appropriate measure?

[parry2k]

As Greg suggest, add another table for unique categories with a number assigned to each category, which you can add index column using edit query and that will get this going. If you share the data in excel sheet, will get you the solution.

[Anonymous]

Hi @parry2k - Here is a sample of the data you requested I send.  Sorry, I had trouble uploading the excel file.

Label	Claim_Type
Need Completed Reimbursement Request Form	Manual
Need Completed Reimbursement Request Form	Manual
Need Completed Reimbursement Request Form	Manual
Need Completed Reimbursement Request Form	Manual
Need Completed Reimbursement Request Form	Manual
Need Completed Reimbursement Request Form	Manual
Statement/EOB Required	Online
Statement/EOB Required	Online
Statement/EOB Required	Online
Statement/EOB Required	Online
Service after end of Coverage Period	Online
Service after end of Coverage Period	Online
Balance Forward	Online
Other	Online
Payment/Proof of Orthodontia	Online
EOB Required	Manual
Letter of Medical Necessity	Online
No Receipt	Online
Credit Card Receipts	Online
Estimated Statement/EOB	Manual