topic Re: Cumulative Total in DAX Commands and Tips

Cumulative Total

mol_ad — Wed, 03 Apr 2024 16:40:46 GMT

I am trying to create a Pareto Chart using Dax Formulas for the set of 5 metrics in a column (Error Column below)...I have created the following Dax Measures:

Error Volume = Data from the data source table
Running Total =

VAR CurrentCount = [Error Volume]

RETURN

CALCULATE([Error Volume],

FILTER(

ALL('Follow-Up Team'[Errors]),

[Error Volume]>= CurrentCount

))

Chart % =

VAR ErrorCount = [Error Volume]

VAR AllErrors = CALCULATE([Error Volume],ALLSELECTED('Follow-Up Team'))

RETURN

DIVIDE(

SUMX(

FILTER(

SUMMARIZE(ALLSELECTED('Follow-Up Team'),'Follow-Up Team'[Errors],

"Volume",'Follow-Up Team'[Error Volume]),

[Volume] >= ErrorCount),

[Volume]),

AllErrors,0)

Below is the cooresponding table for a given filtered time period.

Errors Error Volume Running Total Chart %

#1 9 9 26%

#2 8 25 71%
#3 8 25 71%

#4 6 31 89%

#5 4 35 100%

I need the Running Total for Error #2 = 17 (9+8) and Chart % for Error #2 = 49%.

Re: Cumulative Total

Jihwan_Kim — Wed, 03 Apr 2024 18:18:38 GMT

Hi,

Please check the below picture and the attached pbix file.

WINDOW function (DAX) - DAX | Microsoft Learn

Running total: = CALCULATE ( SUM ( 'Follow-Up Team'[Error Volume] ), WINDOW ( 1, ABS, 0, REL, ALL ( 'Follow-Up Team' ), ORDERBY ( CALCULATE ( SUM ( 'Follow-Up Team'[Error Volume] ) ), DESC, 'Follow-Up Team'[Errors], ASC ), , , MATCHBY ( 'Follow-Up Team'[Errors] ) ) )

ratio: = DIVIDE ( [Running total:], CALCULATE ( SUM ( 'Follow-Up Team'[Error Volume] ), ALL ( 'Follow-Up Team' ) ) )

Re: Cumulative Total

mol_ad — Wed, 03 Apr 2024 19:13:24 GMT

While testing the provided solution, I am getting the below display error.

Re: Cumulative Total

Anonymous — Thu, 04 Apr 2024 06:17:31 GMT

Hi mol_ad ,

May I ask what scenario you are testing? I used the solution provided by Jihwan_Kim and everything shows up fine.

Best Regards,
Adamk Kong

Re: Cumulative Total

mol_ad — Thu, 04 Apr 2024 12:41:01 GMT

I believe I have found the reason I was getting the error. I only provided information that I was using after a filter and not the entire data source table. Since this is first time I am posting, I have not yet figured out how to attach a file, so I will provide a sample of the excel file data source.

*Note: I have provided 2 Users for a 3-Month sample, but I have a total 28 Users with more to be added in the future*

Unique Identifier	User	Supervisor	Month	Errors	Error Volume	Efficiency Score	Quarter
T.B.-January	T.B.	Supervisor #1	January	Timeliness	2	60%	Q1
T.B.-January	T.B.	Supervisor #1	January	Appropriate Activity Code	0	100%	Q1
T.B.-January	T.B.	Supervisor #1	January	Appropriate Notes	1	80%	Q1
T.B.-January	T.B.	Supervisor #1	January	Appropriate Escalation	1	80%	Q1
T.B.-January	T.B.	Supervisor #1	January	Resolution	0	100%	Q1
T.B.-February	T.B.	Supervisor #1	February	Timeliness	2	60%	Q1
T.B.-February	T.B.	Supervisor #1	February	Appropriate Activity Code	2	60%	Q1
T.B.-February	T.B.	Supervisor #1	February	Appropriate Notes	3	40%	Q1
T.B.-February	T.B.	Supervisor #1	February	Appropriate Escalation	0	100%	Q1
T.B.-February	T.B.	Supervisor #1	February	Resolution	1	80%	Q1
T.B.-March	T.B.	Supervisor #1	March	Timeliness	3	40%	Q1
T.B.-March	T.B.	Supervisor #1	March	Appropriate Activity Code	0	100%	Q1
T.B.-March	T.B.	Supervisor #1	March	Appropriate Notes	2	60%	Q1
T.B.-March	T.B.	Supervisor #1	March	Appropriate Escalation	0	100%	Q1
T.B.-March	T.B.	Supervisor #1	March	Resolution	1	80%	Q1
L.B.-January	L.B.	Supervisor #2	January	Timeliness	1	80%	Q1
L.B.-January	L.B.	Supervisor #2	January	Appropriate Activity Code	0	100%	Q1
L.B.-January	L.B.	Supervisor #2	January	Appropriate Notes	2	60%	Q1
L.B.-January	L.B.	Supervisor #2	January	Appropriate Escalation	1	80%	Q1
L.B.-January	L.B.	Supervisor #2	January	Resolution	0	100%	Q1
L.B.-February	L.B.	Supervisor #2	February	Timeliness	3	40%	Q1
L.B.-February	L.B.	Supervisor #2	February	Appropriate Activity Code	0	100%	Q1
L.B.-February	L.B.	Supervisor #2	February	Appropriate Notes	0	100%	Q1
L.B.-February	L.B.	Supervisor #2	February	Appropriate Escalation	0	100%	Q1
L.B.-February	L.B.	Supervisor #2	February	Resolution	1	80%	Q1
L.B.-March	L.B.	Supervisor #2	March	Timeliness	0	100%	Q1
L.B.-March	L.B.	Supervisor #2	March	Appropriate Activity Code	1	80%	Q1
L.B.-March	L.B.	Supervisor #2	March	Appropriate Notes	3	40%	Q1
L.B.-March	L.B.	Supervisor #2	March	Appropriate Escalation	2	60%	Q1
L.B.-March	L.B.	Supervisor #2	March	Resolution	0	100%	Q1

This chart shows what I expect the table and therefore Pareto Chart to look like.

Error E.V. .T. Chart %

Timeliness	11	11	34.38%
Appropriate Notes	11	22	68.75%
Appropriate Escalation	4	26	81.25%
Appropriate Activity Code	3	29	90.63%
Resolution	3	32	100.00%

Re: Cumulative Total

Anonymous — Fri, 05 Apr 2024 07:53:29 GMT

Hi mol_ad ,

You can try formula like below:

Running Total = VAR CurrentError = SELECTEDVALUE ( 'YourTableName'[Errors] ) RETURN CALCULATE ( SUM(YourTableName[Error Volume]), FILTER ( ALLSELECTED ( 'YourTableName'[Errors] ), 'YourTableName'[Errors] <= CurrentError ) )

ratio: = DIVIDE ( [Running total], CALCULATE ( SUM ( 'YourTableName'[Error Volume] ), ALL ( 'YourTableName' ) ) )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Cumulative Total

johnbasha33 — Fri, 05 Apr 2024 09:25:08 GMT

mol_ad

Running Total =
VAR CurrentError = MAX('Follow-Up Team'[Errors])
RETURN
CALCULATE(
SUM('Follow-Up Team'[Error Volume]),
FILTER(
ALL('Follow-Up Team'[Errors]),
'Follow-Up Team'[Errors] <= CurrentError
)
)

Re: Cumulative Total

mol_ad — Fri, 05 Apr 2024 18:55:02 GMT

Thank you! However, I am attempting to create a Pareto Chart with the data and therefore will be sorting the Errors based on Error Volume from largest to smallest and not alphabetically.

Below is what I am looking to accomplish.

Error E.V. R.T. Chart %

Timeliness	11	11	34.38%
Appropriate Notes	11	22	68.75%
Appropriate Escalation	4	26	81.25%
Appropriate Activity Code	3	29	90.63%
Resolution	3	32	100.00%

Here is the resulting table I get when sorting the Errors by Error Volume:

Errors	Error Volume	Running Total
Appropriate Notes	11	18
Timeliness	11	32
Appropriate Escalation	4	7
Appropriate Activity Code	3	3
Resolution	3	21
Total	32

Re: Cumulative Total

mol_ad — Tue, 09 Apr 2024 12:31:13 GMT

When using this formula, I am still running into the same problem. When the errors are sorted alphabetically it is working as expected. However, I am not sorting alphabetically - I am sorting by error volume (see the following):

This is what I am looking for:

Error E.V. R.T. Chart %

Timeliness	11	11	34.38%
Appropriate Notes	11	22	68.75%
Appropriate Escalation	4	26	81.25%
Appropriate Activity Code	3	29	90.63%
Resolution	3	32	100.00%

Here is the resulting table I get when sorting the Errors by Error Volume using your given formula:

Errors	Error Volume	Running Total
Appropriate Notes	11	18
Timeliness	11	32
Appropriate Escalation	4	7
Appropriate Activity Code	3	3
Resolution	3	21
Total	32