Cumulative Total

[mol_ad]

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%.

[johnbasha33]

@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
)
)

[mol_ad]

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

[Anonymous]

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

[mol_ad]

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%

[Anonymous]

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.

[mol_ad]

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

[mol_ad]

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

[Jihwan_Kim]

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' ) )
)