POWER BI COURSE · LESSON 17
Goal: Compare actual sales with a monthly goal. You will build a KPI, a gauge and a clear status label, then explain what the indicator means. A KPI needs both a result and a target; a big number alone is just a card.
March sales against goal
Gap: 50 below target. The bar shows progress; the words give the exact meaning.
1. Enter a small monthly dataset
Choose Home → Enter data, name the table SalesGoals and use the dates and values below. Set MonthStart to the Date type and Actual and Target to whole numbers.
| MonthStart | Actual | Target |
|---|---|---|
| 2026-01-01 | 400 | 450 |
| 2026-02-01 | 500 | 480 |
| 2026-03-01 | 600 | 650 |
Use Modeling → New measure to add these measures one at a time:
Actual Sales = SUM(SalesGoals[Actual]) Sales Target = SUM(SalesGoals[Target]) Target Attainment = DIVIDE([Actual Sales], [Sales Target]) Sales Gap = [Actual Sales] - [Sales Target]
Format Target Attainment as a percentage with one decimal place. DIVIDE handles a zero target more safely than using /.
2. Build the KPI visual
- Insert a KPI visual.
- Put
[Actual Sales]in Value/Indicator,SalesGoals[MonthStart]in Trend axis and[Sales Target]in Target. The field labels can vary slightly by Power BI version. - Sort the underlying trend by MonthStart in ascending order. A KPI focuses on the latest point, so the order matters.
- Set the title to Monthly sales vs target and choose High is good, because higher sales are the desired direction.
The final month is March: actual 600, target 650. The KPI should indicate that the goal was missed. If it instead shows 1,500, check the date trend axis and sorting.
3. Add a gauge and a precise label
Add a Gauge with [Actual Sales] as Value and [Sales Target] as Target value. Add a MonthStart slicer and select March so the gauge compares 600 with 650, rather than the sum of all months. Set a sensible maximum above 650, such as 800, if the automatic scale makes the result hard to read. Put a Card with [Target Attainment] nearby: it should read 92.3%. Another card with [Sales Gap] should read -50.
4. Explain the result in words
Write a small text box: “March sales reached 92.3% of the goal, missing by 50.” Do not rely on color alone. Some readers cannot distinguish red and green, and the exact gap helps everyone understand the result. If your metric is a waiting time or error count, set the KPI direction to Low is good instead.
Practice and common mistakes
- Select February. The goal was exceeded by 20; attainment is about 104.2%.
- If you see a blank KPI, check the trend field is a real date and that Actual and Target are numeric.
- If a gauge and KPI disagree, confirm that both use the same month filter and measures.
← Power BI course roadmap · Review card visuals · Review DAX measures
Further reading: Microsoft: KPI visuals · Microsoft: radial gauges. Example data and diagram are original to this blog.
No comments:
Post a Comment
Please do not enter any spam link in the comment box.