Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Create a Running Total

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.

 

LabelClaim_TypeCountRunning Total
Need Completed Reimbursement Request FormManual66
Statement/EOB RequiredOnline410
Service after end of Coverage PeriodOnline212
Balance ForwardOnline113
OtherOnline114
Payment/Proof of OrthodontiaOnline115
EOB RequiredManual116
Letter of Medical NecessityOnline117
No ReceiptOnline118
Credit Card ReceiptsOnline119
Estimated Statement/EOBManual1

20

Need Help

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        parry2k
        Super User
        8 years ago

        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.

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

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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.