Forum Discussion

Salle's avatar
Salle
Icon for Advocate I rankAdvocate I
2 years ago
Solved

Inaccurate Cumulative Sum

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! 

question
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

3 Replies

  • Thennarasu_R's avatar
    Thennarasu_R
    Icon for Responsive Resident rankResponsive Resident
    2 years ago

    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. 


    • Salle's avatar
      Salle
      Icon for Advocate I rankAdvocate I
      2 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.