Cumulative/Running Total for Pareto

[Anonymous]

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

[v-piga-msft · Accepted solution]

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

[Anonymous]

Hi 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

[Anonymous]

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,
Eric