Forum Discussion
Cumulative/Running Total for Pareto
I'm having trouble creating a cumulative/running total. My DAX formula currently looks like this.
Cumulative Denials = CALCULATE([Total Denials], FILTER(ALLEXCEPT(Denials, Denials[LABEL]), [Count Denials] >= MAX(Denials[LABEL])))
No errors come up until I go to add the measure to the table visualization which then shows as follows.
This is an example on my data set. Since there are no numbers within the data to sum I've created a measure to count the Label column. I've been able to create a count, total sum of the label, and rank. I'm stuck on creating the cumulative/running total as there are no dates in the data I'm wanting to create a Pareto chart for. To throw a little wrinkle into the situation, any Label with an Acct Type of "HSA" will need to be excluded from the cumulative total.
| Acct Type | Label |
| HRA | Need Completed Reinbursement Request Form |
| HSA | Advance Reimbursement |
| HSA | HSA Manual Claim |
| DCRA | Need Completed Reinbursement Request Form |
| HRA | Statement/EOB Required |
| DCRA | Balance Forward |
| DCRA | Other |
| HCRA | Other |
| HCRA | EOB Required |
| DCRA | No Receipt |
| HRA | Need Completed Reinbursement Request Form |
| HSA | Advance Reimbursement |
| DCRA | Balance Forward |
| DCRA | Other |
| HCRA | Other |
| HCRA | EOB Required |
| DCRA | No Receipt |
| HRA | Need Completed Reinbursement Request Form |
| HSA | Advance Reimbursement |
| HSA | HSA Manual Claim |
| DCRA | Need Completed Reinbursement Request Form |
| HRA | Statement/EOB Required |
| DCRA | Balance Forward |
| DCRA | Other |
Hi Anonymous,
This error should be caused by the [Count Denials] >= MAX(Denials[LABEL]))), the Denials[LABEL] is the text type, you cannot use it like that.
You could have a good reference of this bolg Pareto Charting in PowerBI which contains a sample.
If you still need help, please feel free to ask.
Best regards,
Cherry
3 Replies
v-piga-msftResident Rockstar
Hi Anonymous,
This error should be caused by the [Count Denials] >= MAX(Denials[LABEL]))), the Denials[LABEL] is the text type, you cannot use it like that.
You could have a good reference of this bolg Pareto Charting in PowerBI which contains a sample.
If you still need help, please feel free to ask.
Best regards,
Cherry
Hi Cherry (v-piga-msft),
Thanks for the article it was extremely helpful; howver, I'm still running into a problem that I can't figure out. Since my data doesn't have numbers to calculate off of, I need to create a count of the Label column instead of a sum which I was able to do without issue. Here is an example of what my data looks like after creating my new table from the original table with the rank included.
Blend Client Acct Type Label Count Rank 123-DCRA-A 123 DCRA A 1 15 123-HCRA-A 123 HCRA A 6 9 123-HRA-A 123 HRA A 12 6 123-HSA-A 123 HSA A 3 14 123-HCRA-B 123 HCRA B 14 5 456-HRA-B 456 HRA B 5 12 456-HSA-B 456 HSA B 6 9 456-HRA-C 456 HRA C 48 1 456-HSA-C 456 HSA C 15 4 456-HSA-D 456 HSA D 25 2 789-HRA-A 789 HRA A 19 3 789-HRA-B 789 HRA B 5 12 789-HCRA-B 789 HCRA B 8 8 789-HRA-C 789 HRA C 6 9 789-HCRA-C 789 HCRA C 12 6 What I'm trying to accomplish is to report on the Label for each Client and filter out the HSA Acct Type values for all. Since this is one piece of a larger scorecard, I need to be able to filter on Client accross the entirety of the scorecard and rank the Label within each Client. As you can see above this isn't being accomplished since it it ranking the Label across all Clients and Acct Types. So based off the above data I would be looking for the data to be presented below as if it was filtered for each client.
Client 123 Client 456 Client 789 Label Count Rank Run Total Run % Label Count Rank Run Total Run % Label Count Rank Run Total Run % A 19 1 19 58% C 48 1 48 91% A 19 1 19 38% B 14 2 33 100% B 5 2 53 100% C 18 2 37 74% Total 33 Total 53 B 13 3 50 100% Total 50 I tried using the "Running Total" quick measure, but it would not show an individual line for each summed Label, but rather it would show each instance of the Label as shown below in the table visualization.
Label Count Run Total Rank A 19 38 3 A 12 19 6 A 6 7 9 A 1 1 15 B 14 32 5 B 8 18 8 B 5 10 12 B 5 5 12 C 48 66 1 C 12 18 6 C 6 6 9 Any help would be greatly appreciated.
Thanks,
EricHi Cherry (v-piga-msft) - I ended up modifying my data and was able to accomplish what I needed with the article you provided. Thanks for the help!
Regards,
Eric