Mitchell Pearson walks through an end-to-end guide on how to model and visualize forecasting data alongside goals and actual performance in Power BI. This detailed breakdown offers practical modeling strategies, smart DAX tips, and creative chart techniques for anyone looking to improve their Power BI dashboards.
Modeling Your Goal and Forecast Data
- Mitchell starts by emphasizing the importance of treating both goal and forecast data as fact tables.
- He introduces the star schema modeling approach, a critical foundation in Power BI.
- The goal table example includes a few records for 2025 but can be expanded to include dimensions like department, employee, or state.
- Forecast data includes future months like May to September, allowing for future-looking analysis.
Visualizing the Data
Once the data model is in place, Mitchell moves to visualization:
- Uses a Combo Chart (Stacked Column and Line Chart) to overlay metrics.
- Filters the dataset to a specific time window (e.g., Jan–Aug 2025) for clarity.
- Adds in Actual Sales, Forecast, and a calculated Goal Difference metric to track underperformance.
- Uses DAX to calculate Goal Difference: If actual is below the goal, it shows how much was missed.
Adding Style & Clarity
- Color coding: Green for Actual, Blue for Forecast, Red for Missed Goals.
- Data labels are turned on and stylized for better readability.
- A Year-to-Date (YTD) Average is added as a line chart with a separate Y-axis for additional insight.
Advanced Trick: Fillable Bars Without Custom Visuals
To meet a customer's request for a "fillable" goal chart, Mitchell demonstrates a powerful technique using the Clustered Column and Line Chart:
- Creates overlapping bars for Goal and Actual so that Actual appears to "fill up" the Goal bar.
- Sets the Goal bar color to white with a border, and places the Actual bar on top for a visual progress indicator.
- Utilizes chart formatting to overlap columns and control spacing.
Bonus Tip: Leverage the "Details" Feature
Mitchell showcases a lesser-known feature in Power BI called Details under Data Labels:
- Turning on Details allows you to show total values alongside your actual performance.
- This feature adds more context but does not provide the fill-up bar visual effect requested by some clients.
Final Thoughts
Mitchell wraps up by comparing the two approaches: stacked column vs. fillable bar. While custom visuals exist to achieve advanced designs, his in-built Power BI trick is a clean, no-cost solution. The tutorial is filled with actionable advice for enhancing your data storytelling and visual clarity within Power BI dashboards.
Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power BI and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.
Leave a comment