The request
From Marcus Bell, Head of Sales
“We agreed on the KPI tile and a line chart under it. Now build the numbers: 2025 sales by month, a target of last year's same month plus 10%, and % of target. Same measures for the tile and the chart, so they can't disagree.”
Your goal
Step 1 of 3. Create a measure called Sales: the sum of order_items[line_total] for completed orders only (orders[status] = "completed").
- Fill
[Sales]on every row of the visualnot done yet - The Total row right toonot done yet
Step 2 of 3. Add a measure called Sales Target: [Sales] for the same period last year, times 1.1.
- Fill
[Sales Target]on every row of the visualnot done yet - The Total row right toonot done yet
Step 3 of 3. Add a measure called Target Achieved %: [Sales] divided by [Sales Target], safe when the target is blank.
- Fill
[Target Achieved %]on every row of the visualnot done yet - The Total row right toonot done yet
Step 1 · your measure
- Visual
- A matrix: rows = 'Date'[Month]; values = [Sales]
- Slicers
- 'Date'[Year] = 2025
Data pane: tables and columns
- order_items order_item_id, order_id, product_id, quantity, unit_price, discount_pct, line_total
- orders order_id, customer_id, order_date, status, channel, shipping_fee, coupon_code
- products product_id, name, category, subcategory, unit_price, unit_cost, launched_on, is_active
- customers customer_id, first_name, last_name, email, city, state, signup_date, segment, referral_source
- returns return_id, order_item_id, returned_on, reason, refund_amount
- Date Date, Year, Quarter, Month Number, Month, Year Month, Year Quarter, Day, Weekday Number, Weekday
Loading the editor…
Reference solution
| Month | Sales |
|---|---|
| Jan | |
| Feb | |
| Mar | |
| Apr | |
| May | |
| Jun | |
| Jul | |
| Aug | |
| Sep | |
| Oct | |
| Nov | |
| Dec | |
| Total |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
Step 2 · your measure
- Visual
- A matrix: rows = 'Date'[Month]; values = [Sales Target]
- Slicers
- 'Date'[Year] = 2025
Data pane: tables and columns
- order_items order_item_id, order_id, product_id, quantity, unit_price, discount_pct, line_total
- orders order_id, customer_id, order_date, status, channel, shipping_fee, coupon_code
- products product_id, name, category, subcategory, unit_price, unit_cost, launched_on, is_active
- customers customer_id, first_name, last_name, email, city, state, signup_date, segment, referral_source
- returns return_id, order_item_id, returned_on, reason, refund_amount
- Date Date, Year, Quarter, Month Number, Month, Year Month, Year Quarter, Day, Weekday Number, Weekday
Loading the editor…
Reference solution
| Month | Sales Target |
|---|---|
| Jan | |
| Feb | |
| Mar | |
| Apr | |
| May | |
| Jun | |
| Jul | |
| Aug | |
| Sep | |
| Oct | |
| Nov | |
| Dec | |
| Total |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
Step 3 · your measure
- Visual
- A matrix: rows = 'Date'[Month]; values = [Target Achieved %]
- Slicers
- 'Date'[Year] = 2025
Data pane: tables and columns
- order_items order_item_id, order_id, product_id, quantity, unit_price, discount_pct, line_total
- orders order_id, customer_id, order_date, status, channel, shipping_fee, coupon_code
- products product_id, name, category, subcategory, unit_price, unit_cost, launched_on, is_active
- customers customer_id, first_name, last_name, email, city, state, signup_date, segment, referral_source
- returns return_id, order_item_id, returned_on, reason, refund_amount
- Date Date, Year, Quarter, Month Number, Month, Year Month, Year Quarter, Day, Weekday Number, Weekday
Loading the editor…
Reference solution
| Month | Target Achieved % |
|---|---|
| Jan | |
| Feb | |
| Mar | |
| Apr | |
| May | |
| Jun | |
| Jul | |
| Aug | |
| Sep | |
| Oct | |
| Nov | |
| Dec | |
| Total |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
What Marcus gets, and why
The lesson
Marcus has chosen the visuals: a KPI tile at the top and a line chart of sales against target below it. Visuals are only as good as the measures in them, and the same three measures should feed both, so the tile and the chart never tell different stories.
What you are working in
This is a practice report built for this site; Power BI Desktop runs only on Windows. The matrix shows 2025 by month and stands in for the data behind both visuals. A DAX engine evaluates each measure in every cell following Power BI’s rules. Each step checks the last measure in the editor, so keep the earlier ones. In Power BI Desktop: Home → New measure for each, then build the visuals from the Visualizations pane.
Step 1: one definition of Sales
The data has cancelled, refunded and pending orders. Sales means completed orders only, so the
filter goes into the measure once with CALCULATE. Every later measure calls [Sales] instead of
repeating the filter.
Step 2: a target that follows the calendar
Marcus’s rule is “last year’s same month plus 10%”:
Sales Target = CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date])) * 1.1SAMEPERIODLASTYEAR takes the dates in the cell (March 2025) and returns the same dates a year
earlier (March 2024); CALCULATE evaluates [Sales] for those. On the Total row the cell holds all
of 2025, so the target is all of 2024 plus 10%. The Date table is marked as a date table, which
time intelligence needs.
Step 3: the ratio, safely
Target Achieved % = DIVIDE([Sales], [Sales Target])DIVIDE returns a blank when the target is blank or zero, where / would return an error or
infinity. Format it as a percentage.
Wiring the visuals
- KPI visual: Sales in Value, Month (or Date) in Trend axis, Sales Target in Target. The tile shows the latest month, coloured against the target.
- Line chart: Month on the X-axis, Sales and Sales Target in Y-axis. Two lines: where the actual line dips below the target line, the KPI turns red.
- A card or a column chart of Target Achieved % if the team prefers one number per month.
What the numbers say
Read your matrix before you build the visuals. Most months of 2025 beat the target, but the two biggest months, November and December, came in under it. The year as a whole lands at about 105%, so a yearly card would look fine while the holiday season, the part that matters most, missed. That is why Marcus wanted a monthly trend under the headline number.
Lantern lit!
Practised: kpi visualtargetsameperiodlastyeardivideline chartboss
- Solved
- No hints
- First run