Pareto Chart of RunningAverage

[Anonymous]

Hi Community,

I have Table as follows. 

QA_Score	Error_Category	Provider_ID
0	EC1	1001-1005
0	EC1	1001-1006
0	EC1	1001-1007
2.5	EC1	1003-1007
2.5	EC1	1003-1056
2.5	EC1	1004-1002
5	EC1	1003-1007
5	EC1	1004-1008
5	EC1	1004-1009
5	EC1	1004-1011
7.5	EC1	1004-1012
7.5	EC1	1005-1001
7.5	EC2	1003-1007
7.5	EC2	1004-1002
10	EC1	1003-1007
10	EC1	1004-1002
10	EC1	1004-1011
10	EC1	1004-1012
12.5	EC1	1016-1001
12.5	EC1	1042-1067
12.5	EC2	1003-1007
15	EC2	1003-1007
15	EC2	1004-1002
15	EC2	1004-1011
15	EC2	1004-1012
20	EC2	1003-1007
20	EC2	1004-1002
25	EC2	1042-1067

Here, I am trying to create pareto chart (using Line and clustered column chart) with Error_Category as Shared axis, AVG_QA_Score as Column values, and Pareto% as line values.

I have used the following DAX as per this (7:15 onwards) for the table that I have attached in the screenshot.

AVG_QA_Score = AVERAGE(Table[QA_Score])

RankAVG_QA_Score = RANKX(ALL(Table[Error_Category]), [AVG_QA_Score])

CumulativeAVG_QA_Score = CALCULATE([AVG_QA_Score], TOPN([RankAVG_QA_Score], ALL(Table[Error_Category]),[AVG_QA_Score]))

Pareto% = DIVIDE([CumulativeAVG_QA_Score], CALCULATE([AVG_QA_Score], ALL(Table[Error_Category])))

Here, I want the cumulative AVG_QA_Score, but it is rather showing me as cumulative average since I have used AVERAGE in the AVG_QA_Score.  Is there anyway I can get the cumulative AVG_QA_Score so I can put it as Pareto% on Line values??

Thanks in advance. 

[Fowmy · Accepted solution]

@Anonymous 

Can you use the below measure instead?

CumulativeAVG_QA_Score = 

VAR R= [RankAVG_QA_Score]
RETURN
SUMX(
    FILTER(
    SUMMARIZE(ALL(Table2),Table2[Error_Category]),
    [RankAVG_QA_Score] <= R),
     [AVG_QA_Score]
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Fowmy]

@Anonymous 

Can you try the following measure for the cumulative Average?

CumulativeAVG_QA_Score = 

VAR R= [RankAVG_QA_Score]
RETURN
CALCULATE(
    [AVG_QA_Score], 
    FILTER(
    SUMMARIZE(ALL(Table2),Table2[Error_Category]),
    [RankAVG_QA_Score] <= R)
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

Awesome @Fowmy Thank you.

Can you check the pareto %age once please. It should add up to 100% at the last rank as per this video (Time - 15:00)

[Fowmy]

@Anonymous 

I didn't actually see the video, use the measure for Cumm %

Pareto% = 
DIVIDE(
    [CumulativeAVG_QA_Score], 
    CALCULATE(
        [CumulativeAVG_QA_Score],
        ALLSELECTED(Table2[Error_Category]))
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

Hi @Fowmy ,  

I tried the above formula for cumm %, but it is giving me the required percentage.  Please take a look at the shared screenshot.  If the link for the pbix is not working,  I am sharing some more data below.  The formulas for the measures are same as above.  

1.  In the shared screenshot, the CumulativeAVG_QA_Score should be 29.63+4.38 = 34.01.  I am not able to understand why it is showing as 34.00 !!!

2.  I need the % to be cumulative.  For the 1st rank (EC2), it should be 10.00/38.07 = 26.26 %.  Similary for the 2nd rank (EC1), it should be 16.88/38.07 = 44.33%, and 3rd rank (EC3), it should be 23.38/38.07 = 61.41%, and so on.  Note:  Here, 38.07 is the total of all AVG_QA_Score.

QA_ScoreError_CategoryProvider_ID

0	EC1	1001-1005
0	EC1	1001-1006
2.5	EC1	1001-1005
2.5	EC1	1003-1007
5	EC1	1003-1007
5	EC1	1004-1008
7.5	EC1	1004-1012
7.5	EC1	1005-1001
10	EC1	1003-1007
10	EC1	1004-1002
12.5	EC1	1016-1001
20	EC1	1042-1067
0	EC2	1001-1005
0	EC2	1001-1006
2.5	EC2	1001-1005
2.5	EC2	1003-1007
5	EC2	1003-1007
5	EC2	1003-1052
7.5	EC2	1003-1007
7.5	EC2	1004-1002
10	EC2	1003-1007
10	EC2	1003-1054
12.5	EC2	1003-1007
12.5	EC2	1004-1002
15	EC2	1003-1007
15	EC2	1004-1002
20	EC2	1003-1007
20	EC2	1004-1002
25	EC2	1042-1067
0	EC3	1001-1005
0	EC3	1001-1006
2.5	EC3	1003-1007
2.5	EC3	1004-1002
5	EC3	1003-1007
5	EC3	1004-1002
10	EC3	1003-1007
10	EC3	1003-1054
15	EC3	1003-1007
15	EC3	1111-1001
0	EC4	1001-1005
0	EC4	1001-1006
2.5	EC4	1003-1007
2.5	EC4	1004-1002
5	EC4	1003-1007
5	EC4	1004-1002
10	EC4	1003-1007
10	EC4	1004-1002
0	EC5	1001-1005
0	EC5	1001-1006
2.5	EC5	1001-1005
2.5	EC5	1001-1006
5	EC5	1001-1007
5	EC5	1003-1007
7.5	EC5	1003-1007
7.5	EC5	1004-1011
10	EC5	1004-1058
10	EC5	1051-1049
12.5	EC5	1003-1007
12.5	EC5	1004-1058
0	EC6	1001-1005
0	EC6	1001-1006
2.5	EC6	1003-1007
2.5	EC6	1004-1011
5	EC6	1003-1007
5	EC6	1004-1002
7.5	EC6	1003-1007
10	EC6	1042-1067

Please take a look.  Thank you.

.

[Anonymous]

@Fowmy Its giving the same cumulative average.

[Fowmy]

@Anonymous 

As per the sample you shared, my results show as below. Error Code EC1 is showing 9.29 which is cumulative.
Share more data and expected result so I can cross-check, You share a sample PBIX file if possible.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

Hi @Fowmy  We want the cumulative AVG_QA_Score.  For the second row, the cumulativeAVG_QA_Score should be like 15.25+5.97, like running total.

Thanks 

[Fowmy · Accepted solution]

@Anonymous 

Can you use the below measure instead?

CumulativeAVG_QA_Score = 

VAR R= [RankAVG_QA_Score]
RETURN
SUMX(
    FILTER(
    SUMMARIZE(ALL(Table2),Table2[Error_Category]),
    [RankAVG_QA_Score] <= R),
     [AVG_QA_Score]
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn