Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Cumulative Sum, DAX

Hi all,

 

I'm having trouble creating a calculated column (or measure) in DAX that will return a running total based on groups.  The data I'm working with looks like this:

 

I need the Requirement Quantity summed up as a running total, which would start over every time the 'Item Number' field changes.  As you can see, I've also added a Conditional Index field through M Query.  But I've tried almost every single DAX formula I've found on this forum and elsewhere and have had absolutely no luck with it.  I managed to create a running total in M Query, but the query takes hours to refresh.  Any ideas on how I can solve this?  Thanks in advance!

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    This is my ussual way of calculating running totals but it should do the trick for you aswell.

    Calculated column =
CALCULATE (
    SUM ( 'Table'[Requirement quantity] );
    // Calculate over the entire table
    ALL ( 'Table' );
    // Filter where item numbers are the same
    'Table'[Item Number] = EARLIER ( 'Table'[Item NumberT] );
    // Filter the order for calculating
    'Table'[Index] <= EARLIER ( 'Table'[Index] )
)

    Always here to help.

    Kind regards

    Joren Venema

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    This is my ussual way of calculating running totals but it should do the trick for you aswell.

    Calculated column =
CALCULATE (
    SUM ( 'Table'[Requirement quantity] );
    // Calculate over the entire table
    ALL ( 'Table' );
    // Filter where item numbers are the same
    'Table'[Item Number] = EARLIER ( 'Table'[Item NumberT] );
    // Filter the order for calculating
    'Table'[Index] <= EARLIER ( 'Table'[Index] )
)

    Always here to help.

    Kind regards

    Joren Venema

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks Joren, this worked!  You're the man!!!

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago
    Total Requirement measure = SUM(table[Requirement Quantity])
======================================================
Total Cumulative =
    VAR MaxDate =
        MAX ( table[Requirement Date] ) -- Saves the last visible date
    VAR ReqQuantity =
        CALCULATE (
            [Total Requirement measure],
            -- Computes hours
            table[Requirement Date] <= MaxDate,
            -- Where date is before the last visible date
            ALLEXCEPT (
                table,
                table[Buyer Group ]
            )
        )
    RETURN
        ReqQuantity

    Hi Anonymous 
    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel