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
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?
Solved! Go to 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
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
@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...
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |