Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
mtmcd
Frequent Visitor

Cumulative Line and Clustered Column Chart

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:ScreenHunter_01 Oct. 23 12.31.jpg

 

 

 

 

 

 

 

I then reshape the data to look like this:

ScreenHunter_02 Oct. 23 12.38.jpg

 

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:

ScreenHunter_03 Oct. 23 12.40.jpg

 

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

1 ACCEPTED SOLUTION

Hi @mtmcd

 

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

 

  report.png

 

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

 

View solution in original post

3 REPLIES 3
BILASolution
Solution Specialist
Solution Specialist

Hi @mtmcd

 

Here is the solution...

 

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

 

data.png

 

 

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

 

 

report.png

 

Regards

BILASolution

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

Hi @mtmcd

 

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

 

  report.png

 

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

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.