The request
From Heather Morgan, Sales Planning Manager
“Finance gave me 2025 targets per category as a little six-row table, and I want bars of % of target on the sales page, plus one KPI tile for the whole store. The target table can't join to products: category isn't unique there. Can you get the targets in?”
Your goal
The model has a table Targets(category, target) with no relationship. The matrix has products[category] on its rows with a slicer on 'Date'[Year] = 2025, next to [Sales]. Create a measure called Target that returns each category's target and, on the Total row, the sum of the visible categories' targets; then a measure called Target Achieved %: [Sales] / [Target]. The last one is checked.
- Fill
[Target Achieved %]on every row of the visualnot done yet - The Total row right toonot done yet
Your measure
- Visual
- A matrix: rows = products[category]; values = [Sales], [Target Achieved %]
- Slicers
- 'Date'[Year] = 2025
- Measures in the report
Sales = CALCULATE(SUM(order_items[line_total]), orders[status] = "completed")
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
- Targets category, target
Loading the editor…
Reference solution
| category | Sales | Target Achieved % |
|---|---|---|
| Decor | $290,301 | |
| Furniture | $573,131 | |
| Kitchen | $292,808 | |
| Lighting | $385,145 | |
| Outdoor | $507,358 | |
| Textiles | $251,426 | |
| Total | $2,300,170 |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
What Heather gets, and why
The lesson
A KPI visual or a ”% of target” bar needs a target measure, and targets usually come from a spreadsheet someone in finance maintains. Heather’s has one row per category. The data model has no relationship to it, so the measure has to fetch each target itself.
What you are working in
This page is a practice report built for this site; Power BI Desktop runs only on Windows. The Targets table is loaded next to the store tables, and a DAX engine evaluates your measures in each cell, following Power BI’s rules. In Power BI Desktop you’d bring the table in with Home → Get data → Excel workbook (or Home → Enter data for six rows), then create both measures with Home → New measure.
Why not a relationship?
products[category] repeats on about twenty products, and Targets[category] is unique. A relationship
from products[category] (many) to Targets[category] (one) is legal, but its filter would flow
from Targets to products. Heather’s rows are products[category], which can’t filter Targets that
way. The clean model fix is a separate Category table that relates to both; for one measure,
LOOKUPVALUE is enough.
LOOKUPVALUE: like XLOOKUP in Excel
LOOKUPVALUE(Targets[target], Targets[category], "Lighting")returns 360,000: the value of Targets[target] on the row where Targets[category] is Lighting.
The arguments are the column to return, then pairs of “search column, value to find”.
One target per category, a sum for the Total
Target =
SUMX(
VALUES(products[category]),
LOOKUPVALUE(Targets[target], Targets[category], products[category])
)On the Lighting row, VALUES(products[category]) holds one value, so SUMX looks up one target.
On the Total row it holds six, so SUMX looks up six and adds them: 2,270,000. With a slicer on two
categories, the Total is the sum of those two targets. The KPI tile and the bars use the same
measure.
The attainment
Target Achieved % = DIVIDE([Sales], [Target])Format it as a percentage (Measure tools → Format → Percentage). Above 100% means the category beat its target. For the bars, sort by this measure; for a KPI visual, put Sales in Value and Target in Target.
Common mistakes
- SELECTEDVALUE in the lookup. Fine on each row, blank on the Total.
- SUM(Targets[target]) with no lookup. The rows don’t filter Targets, so every category is divided by the store’s whole target.
- Typing targets into the measure.
SWITCH(category, "Decor", 300000, …)works until finance changes a number; keep them in a table.
Lantern lit!
Practised: targetlookupvaluesumxvalueskpi visualdivide
- Solved
- No hints
- First run