@x2kibail
To achieve your goal of adding a line in a bar chart visual to track target vs. actual completions of orders in Power BI, you'll need to create a few DAX measures. These measures will help you visualize both the actual number of completed orders and the expected target for any given time point (week or month).
Here's how you can create these measures:
1. Calculate Target Completions:
First, you need a measure to calculate the target completions up to a given week or month. This measure will calculate the total orders divided by the number of periods (weeks or months) and then multiply by the elapsed periods.
Target Completions =
VAR TotalOrders = CALCULATE(COUNTROWS('Orders'), 'Orders'[STATUS] = "Complete" || 'Orders'[STATUS] = "Pending")
VAR Periods = DATEDIFF(MIN('Orders'[Completion Date]), MAX('Orders'[Completion Date]), MONTH)
VAR ElapsedPeriods = DATEDIFF(MIN('Orders'[Completion Date]), TODAY(), MONTH) + 1 // Assuming completion date is set and we're using months
RETURN
(TotalOrders / Periods) * ElapsedPeriods
This measure calculates how many orders should have been completed by now based on a simple distribution over time. Adjust `DATEDIFF` parameters if you're tracking by weeks or another time unit.
2. Calculate Actual Completions:
You probably already have this measure, but if not, here’s how you can calculate the actual completions:
Actual Completions =
CALCULATE(
COUNTROWS('Orders'),
'Orders'[STATUS] = "Complete",
ALL('Orders'[Completion Date]) // Removes filters on the date to count all completions
)
3. Combine in a Visual:
To combine these in a bar and line chart:
- Use "Actual Completions" for the bar chart values.
- Use "Target Completions" for the line chart values.
Select the bar and line chart visual in Power BI and drag your "Completion Date" to the axis, "Actual Completions" to the bar values, and "Target Completions" to the line values.
4. Adjusting for Tracking Errors:
If you're behind or ahead of schedule, your target completions will need to be recalculated monthly or weekly. You can adjust the `Target Completions` formula to factor in the difference between actual and planned as you move through the year.
Updated Target Completions =
VAR OriginalTarget = [Target Completions]
VAR CurrentPerformance = [Actual Completions]
VAR AdjustedTarget = IF(CurrentPerformance < OriginalTarget, OriginalTarget + ((OriginalTarget - CurrentPerformance) / (Periods - ElapsedPeriods)), OriginalTarget)
RETURN
AdjustedTarget
This setup allows you to see how well your team is performing against a distributed target of completions across the year. You can adjust these formulas based on your specific data structure and business logic.
If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍
