Cumulative Sum for the same values does not work. PS: I have no date and index field

[Siti]

Hi,

I want to achive cumulative percentage for pareto chart. I am able to calcaulate cumulative sum based on my Total Rejected numeric column. However, it is not working when the values are same in Total Rejected column and then of course my cummulative  % (Pareto Chart) does not return correct value. Below is the snap shot for the same.

Please help on this.

[MFelix]

Hi @Siti ,

What is the formula you are using for the rank and is it a measure or a column no your model?

You need to add a random value in order to not have duplicates and the calculation are correct.

[Siti]

Hi, thank you for your response.

We have not used the rank for cumulative sum, it is just to show the duplicates values.  Below is the excerpt of cumulative logic. Appreciate your time and help.

PS: Cumulative is working fine except for duplicate values. Ranking and Indexing (New Parameter) both didn't work in this case.

VAR CurrentRejected = [Total Rejected]

VAR Summarizetable =

SUMMARIZE(

ALLSELECTED('FPY Rejected'),

'FPY Rejected'[Step],

"Rejected",COUNT('FPY Rejected'[PSID]))

VAR Cummulativesum =

SUMX(

FILTER(Summarizetable,[Rejected]>= CurrentRejected),

[Rejected]

)

[v-deddai1-msft]

Hi @Siti ,

I suggest you use RAND() Function to break ties in your rank column:

First you can create a rank measure :

Rank = RANKX(ALL('FPY Rejected'[Step]),[Total Rejected]+RAND(),,DENSE)

Then use the following measure for your Cummulativesum

VAR Currentrank = [Rank]
VAR Summarizetable =
SUMMARIZE(
ALLSELECTED('FPY Rejected'),
'FPY Rejected'[Step],
"Rejected",COUNT('FPY Rejected'[PSID]),"_rank",[Rank])

VAR Cummulativesum =
SUMX(
FILTER(Summarizetable,[_rank]>= Currentrank),
[Rejected]
)

Return Cummulativesum

For more details, please refer to https://www.youtube.com/watch?v=kRtmb8ftyQc

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

Best Regards,

Dedmon Dai

[Siti]

Thank you for your response. However, it is still not working. Below is the table for your reference.

Step	Rank	Total Rejected	TestCumm	Pareto Chart
POST_IESS_TEST1	81	15	56614	99.47%
TC20_FINAL_ASSEMBLY	80	15	56614	99.47%
SAFETY_TEST_CFC	85	14	56628	99.50%
SAFETY_TEST_ENCLOSURE	85	13	56641	99.52%
LABEL_PRINTING_PRODUCT	86	12	56653	99.54%
CO2_FFU_TEST	89	10	56693	99.61%
KITTING	90	10	56693	99.61%
OQC_(PACKING_INSPECTION)_BTO	88	10	56693	99.61%
UPGRADE_LABEL	88	10	56693	99.61%
TEST_WPU	91	9	56702	99.63%
LABEL_PRINT	93	8	56726	99.67%
PROCESSOR_PCA_TEST	92	8	56726	99.67%
SPO2_FFU_TEST	93	8	56726	99.67%
FUNCTION_TEST_NO_CHAMBER1	98	7	56754	99.72%
LABEL_PRINT_SHIPPING	96	7	56754	99.72%

[v-deddai1-msft]

Hi @Siti ,

Would you please try the following rank meausre ?

Rank = RANKX(ALL('FPY Rejected'[Step]),[Total Rejected]+RANDBETWEEN(1,10000)/10000,,DENSE)

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

Best Regards,

Dedmon Dai

[v-deddai1-msft]

Hi @Siti ,

Would you please show us the whole sample data in pbix by onedrive for business?

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

Best Regards,

Dedmon Dai