Cumulative Total

Question

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 tableRunning Total =&nbsp;VAR CurrentCount = [Error Volume]RETURN&nbsp; &nbsp; CALCULATE([Error Volume],&nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; ALL('Follow-Up Team'[Errors]),&nbsp; &nbsp; &nbsp; &nbsp; [Error Volume]&gt;= CurrentCount&nbsp; &nbsp; ))Chart % =VAR ErrorCount = [Error Volume]VAR AllErrors = CALCULATE([Error Volume],ALLSELECTED('Follow-Up Team'))RETURNDIVIDE(SUMX(&nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; SUMMARIZE(ALLSELECTED('Follow-Up Team'),'Follow-Up Team'[Errors],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Volume",'Follow-Up Team'[Error Volume]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Volume] &gt;= ErrorCount),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Volume]),&nbsp; &nbsp; AllErrors,0) &nbsp; &nbsp;&nbsp;Below is the cooresponding table for a given filtered time period.&nbsp;Errors&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Error Volume&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Running Total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Chart %&nbsp;#1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 9&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 9&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 26%&nbsp;#2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;25&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;71%&nbsp;#3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;25&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;71%&nbsp;#4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;31&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;89%&nbsp;#5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;35&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100%&nbsp;I need the Running Total for Error #2 = 17 (9+8) and Chart % for Error #2 = 49%.

jihwan_kim · Answer

Hi,
Please check the below picture and the attached pbix file.
&nbsp;

&nbsp;
&nbsp;
WINDOW function (DAX) - DAX | Microsoft Learn
&nbsp;
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] )
    )
)
&nbsp;
ratio: = 
DIVIDE (
    [Running total:],
    CALCULATE ( SUM ( 'Follow-Up Team'[Error Volume] ), ALL ( 'Follow-Up Team' ) )
)
&nbsp;

mol_ad · Answer

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

anonymous · Answer

Hi&nbsp;mol_ad&nbsp;,
&nbsp;
May I ask what scenario you are testing? I used the solution provided by&nbsp;Jihwan_Kim&nbsp; and everything shows up fine.

&nbsp;
Best Regards,Adamk Kong

mol_ad · Answer

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 · Answer

Hi&nbsp;mol_ad&nbsp;,
&nbsp;
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] &lt;= CurrentError
        )
    )ratio: =
DIVIDE (
    [Running total],
    CALCULATE ( SUM ( 'YourTableName'[Error Volume] ), ALL ( 'YourTableName' ) )
)

&nbsp;

&nbsp;
Best Regards,Adamk Kong
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

Forum Discussion

Cumulative Total

8 Replies

Related content

Cumulative Total

Cumulative Total

SumX? - cumulative running total

Cumulative Total

Cumulative total

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