Cumulative Line and Clustered Column Chart

[mtmcd]

I'm having a difficult time plotting a cumulative (running total) line on top of a clustered column chart for goal vs actual quantities.

Here is my raw data, which shows Goal vs Actual quantities for a given process, and product category and model:

I then reshape the data to look like this:

Finally, I create my running total column, which does not add the values appropriately:

RunningTotalActual = CALCULATE(SUM('Overall Summary - Formatted'[Quantity]),FILTER(ALL('Overall Summary - Formatted'[Date]),'Overall Summary - Formatted'[Date] <= MAX('Overall Summary - Formatted'[Date])),FILTER(ALL('Overall Summary - Formatted'[Status]),'Overall Summary - Formatted'[Status] = "Actual"),FILTER(ALL('Overall Summary - Formatted'[Process]), 'Overall Summary - Formatted'[Process] = "Wax"))

The cumulative line chart simply matches what the actual values are instead of adding them as time progresses:

Is there an error in my code or the way I have my data set up?

[BILASolution]

Hi @mtmcd

Here is the solution...

1. Sample Data: My table is called "Process"

2. Measures:

Total Quantity = SUM(Process[Quantity]) 

Total Actual = CALCULATE([Total Quantity];ALL(Process[Date].[Day]);Process[Status] = "Actual") 

Total Goal = CALCULATE([Total Quantity];ALL(Process[Date].[Day]);Process[Status] = "Goal") 

3. Final View

Regards

BILASolution

[mtmcd]

@BILASolution,  thank you for taking the time to propose a solution.  However, it does not appear that the "Total Actual" and "Total Goal" lines in your chart are accurate.  They are not plotting the running total of the values over time; they are all a steady amount.

As for my particular application, the Actual and Goal measures are coming up blank using your measures...

Wouldn't I need to implement a new column to calculate running total instead of a measure?

[BILASolution · Accepted solution]

Hi @mtmcd

1. If the results You're looking for is the next picture...

 

try this measures...

Accumulative Actual = var actualdate = FIRSTNONBLANK(Process[Date];1) return CALCULATE([Total Quantity];ALL(Process[Date].[Day]);Process[Status] = "Actual";Process[Date] <= actualdate) 

Accumulative Goal = var actualdate = FIRSTNONBLANK(Process[Date];1) return CALCULATE([Total Quantity];ALL(Process[Date].[Day]);Process[Status] = "Goal";Process[Date] <= actualdate) 

Regards

BILASolution