cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
garynorcross Regular Visitor
Regular Visitor

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:

Capture.JPG

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Joren_venema Regular Visitor
Regular Visitor

Re: Cumulative Sum, DAX

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

View solution in original post

3 REPLIES 3
Nathaniel_C Super Contributor
Super Contributor

Re: Cumulative Sum, DAX

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 @garynorcross 
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

Joren_venema Regular Visitor
Regular Visitor

Re: Cumulative Sum, DAX

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

View solution in original post

garynorcross Regular Visitor
Regular Visitor

Re: Cumulative Sum, DAX

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

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 390 members 3,947 guests
Please welcome our newest community members: