topic Re: Inaccurate Cumulative Sum in DAX Commands and Tips

Inaccurate Cumulative Sum

Salle — Mon, 30 Oct 2023 12:32:59 GMT

Hi, having trouble getting the cumulative sum accurate. Filter issue?

Setup:
Two tables: 'Rerun'[RejectCodeID];[Units] and 'Scrap'[RejectCodeID]; [Units].

One to many: Dimension table 'RejectCode'[RejectCodeID]

The Dax measure I use calculates the cumulative sum of rejects incorrectly after 116.
It sould been 35, 57, 77, 93, 105, 117, 118, 119, 120, 121, 122.

Cumulative Rejects = VAR TotalRejects = [Reruns] + [Scrap] Return SUMX( FILTER( SUMMARIZE( ALLSELECTED('RejectCode'), 'RejectCode'[RejectCodeID], "Rejects", [Scrap] + [Reruns] ), [Rejects] >= TotalRejects ), [Rejects] )

Any ideas on what can be wrong?

Thanks!

Re: Inaccurate Cumulative Sum

Thennarasu_R — Mon, 30 Oct 2023 12:49:33 GMT

Hi Salle

Try this measure ,

Measure=Calculate (Rejects+Scraps,Filter(All(Dimdate),Date column<=Max(Date))

Thanks,
Thennarasu R
If this post helps then please consider accepeted the solution  to help the other members find it more quickly.

Re: Inaccurate Cumulative Sum

Salle — Mon, 30 Oct 2023 13:09:37 GMT

It does not work.
I want to plot a pareto chart with RejectionCodeID as bars and Pareto curve as line. But first the cumulative sum must be accurate.

Re: Inaccurate Cumulative Sum

Anonymous — Wed, 01 Nov 2023 08:34:54 GMT

Hi Salle ,

I'm suspecting you're trying to accumulate based on total largest to smallest, and you can check the results as follows:

Rank = VAR _1=RANKX('Table (2)','Table (2)'[Total],,DESC,Dense) VAR _2 = RANKX('Table (2)','Table (2)'[RejectCodeID],,ASC,Dense)/ POWER(10,ROUNDDOWN(COUNT('Table (2)'[RejectCodeID])/10,0)+1) RETURN _1 + _2 Cumulative Rejects = VAR TotalRejects = [Re] + [Sc] Return CALCULATE( SUM('Table (2)'[Total]), FILTER( ALLSELECTED('Table (2)'), 'Table (2)'[Rank] <= MAX('Table (2)'[Rank]) ) )

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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