The short version
Adventure Works sold $24.91M of goods between January 2020 and June 2022 and kept
$10.46M of it as profit, a margin of 42.0%. Bikes bring in 95% of sales, Australia is the
biggest single region, and December is the strongest month every year. The rest of this post
shows how I built every number with DAX, checked it in a query editor, and put it on a
dashboard.
Aim: To design KPIs and custom DAX calculations on the Adventure Works dataset, validate them
with DAX Studio, and present them in an interactive Power BI dashboard with KPI cards, at
least four visuals and slicers for year, product category and region.
A quick DAX primer
A calculated column is worked out row by row when the data loads and takes up space in
the model. A measure is worked out when a visual asks for it, using whatever filters that
visual applies. Almost every KPI should be a measure.
Two ideas explain most of DAX. Row context means “this one row”, which is what SUMX uses
when it multiplies quantity by price line by line. Filter context means “the rows the visual lets
through”, such as one year or one region. CALCULATE changes the filter context, which is why
it sits behind most of the interesting measures below.
DAX Studio is a separate tool that connects to the open Power BI model. You write a DAX
query, run it, and see the result as a table, which makes it a good place to test a measure
before trusting a card.
The data
The dataset arrives as a set of CSV files that together describe a bicycle company: three
sales files (one per year), lookup tables for products, categories, subcategories, customers
and territories, and a returns table.
The relationships are simple one-to-many links from each lookup table into Sales :
Products[ProductKey] , Territories[SalesTerritoryKey] , Customers[CustomerKey] and
Calendar[Date] to Sales[OrderDate] . Subcategories link to Products and Categories link to
Subcategories. The three sales files are appended into one Sales table in Power Query.
Cleaning checks
- The combined sales table has 56,046 rows with no blank cells and no duplicate rows.
- Every ProductKey and TerritoryKey in Sales finds a match in its lookup table, so nothing drops out of a relationship.
- The customer file is not UTF-8, so I loaded it with the Western European encoding to keep names with accents intact.
- The Sales data stops on 30 June 2022, so 2022 is only a half year. I keep that in mind whenever I compare years.
- The Components category exists in the lookup table but has no sales, so it never shows up in the charts. The calendar file has only a Date column, so I added four calculated columns to give the
- slicers and the monthly axis something to use. The table is marked as the date table, which the time functions need.
Step 1: The ten KPI measures
The data has no sales amount column, so Sales is worked out as quantity times the product price, row by row. RELATED pulls the price from the Products table into each sales row. Cost works the same way, and everything else builds on those two.
Two choices worth stating. Average Sales is the average value of one order line ($444.54),
while Average Order Value is total sales divided by the number of distinct orders ($990.09),
because one order can hold several lines. Average Product Price averages the price list of all 293 products, not the prices of what actually sold.
Step 2: Seventeen more measures
The brief asks for at least twenty measures. These seventeen take the total to twenty-seven.
They cover returns, share of total, filtered totals, ranking and the time-based set used in the next step.
The functions the brief asked for all appear: SUM , AVERAGE , COUNT , DISTINCTCOUNT, CALCULATE, DIVIDE, FILTER and RANKX . I used DIVIDE instead of the / operator
everywhere, since it returns blank instead of an error when the denominator is zero. That matters for the first month of data, which has no previous month.
Step 3: Time-based analysis
Four measures make up the month-over-month view. Monthly Sales is the sales in the month on the axis. Previous Month Sales shifts the date filter back one month with PREVIOUSMONTH .
Sales Variance is the difference, and Sales Growth % is that difference as a share of the previous month.
Monthly sales against the previous month. Sales stayed near $0.5M a month through mid-2021, then climbed to $1.83M in June 2022.
Sales growth % by month. Blue is growth, orange is decline. The same pattern repeats: a big jump in December and a drop in January.
Step 4: Checking everything in DAX Studio
- Open the Power BI file, then open DAX Studio and choose PBI / SSDT Model to connect to it.
- In the left pane, confirm the eight tables and the measures from steps 1 to 3 are listed.
- Write one query that returns all ten KPIs in a single row using EVALUATE ROW(...) .
- Run it with F5 and compare each value with the matching card in Power BI.
- Run a second query with SUMMARIZECOLUMNS to get monthly sales, previous month, variance and growth, and compare it with the table above.
- To look at performance, switch on Server Timings, run the query again, and see how the time splits between the Storage Engine and the Formula Engine.
- A measure that needs a lot of Formula Engine time is the first place to simplify.
Screenshot 1: steps performed. The query that returns the ten KPI measures, with the model tables and measures in the left pane and the result grid below.
Step 5: The Power BI dashboard
Layout
- Top row: KPI cards for Total Sales, Total Profit, Total Orders, Total Customers and Profit Margin %.
- Slicers down the side: Year (from Calendar[Year] ), Product Category and Region.
- Trend: line chart with Month Year on the axis and Monthly Sales and Previous Month Sales as values.
- Comparison: a column chart of Total Sales by category, a bar chart of Total Sales by region, and a bar chart of the top 10 products by Total Sales (a Top N filter on ProductName ).
How I built it
- Load the CSV files from a folder, append the three Sales files, and set column types in Power Query.
- Create the relationships in Model view and mark Calendar as the date table.
- Add the calculated columns to Calendar, then write the measures from steps 1 to 3 in a dedicated measures table.
- Insert one Card visual per KPI and format the numbers: currency for money, whole numbers for counts, two decimals for percentages.
- Add the line, column and bar charts, then three slicers. Slicers filter every visual on the page at once.
- Keep one colour theme: a single accent for the main metric, grey for comparison series, and the same font throughout.
Screenshot 2: output through cards. The ten KPI measures as cards with no slicer applied. They match the DAX Studio result exactly.
What the numbers say
1. Sales stepped up in mid-2021, and 2022 is on track to be the biggest year
Sales were $6.40M in 2020 and $9.32M in 2021, an increase of 46%. The half year of 2022 already reached $9.19M, almost the whole of 2021. Comparing the same six months, January to June 2022 is 211% higher than January to June 2021 ($9.19M against $2.95M).
Sales and profit by year. 2022 covers January to June only.
2. Bikes are the revenue, accessories are the margin
Bikes produce 94.9% of sales ($23.64M) at a 41.1% margin. Accessories are 69% of all units sold but only 3.6% of sales, and they earn the highest margin at 62.8%. Clothing is the smallest category at $0.37M. Accessories and clothing first appear in the data in July 2021, so they only explain a small part of the growth since then. Most of it comes from bikes: bike sales in the second half of 2021 were nearly double those of the first half.
Sales and profit margin by category.
3. Top-selling products
All six Mountain-200 variants sit at the top of the revenue list and together bring in $7.17M.
The Mountain-200 Black, 46 is the number one product at $1.24M. By units it is a different list: the 30 oz water bottle leads with 7,967 units, followed by the patch kit and the mountain tyre tube. Cheap accessories give volume, expensive bikes give revenue.
Top 10 products by sales
4. Australia leads, and two US regions carry the United States
Australia is the largest region with $7.42M, which is 29.8% of sales. The Southwest ($4.82M) and Northwest ($3.10M) are next, and together they make up almost all US sales. The Central, Northeast and Southeast US regions are almost empty, each under $12K. Canada, France and Germany are mid-sized, and the United Kingdom is the strongest European region.
Sales by region
5. Seasonality, order value and returns
December is the peak: sales rose 73% over November in 2020 and 44% in 2021. January then fell 23% and 22%.
Average order value fell **from $2,435 in 2020 to $872 in 2021 and $776 in 2022. Orders more than quadrupled once accessories and clothing arrived, and those orders are small.
**Profitability is steady: the margin was 40.6% in 2020, 42.5% in 2021 and 42.3% in 2022.
Returns are low: 1,828 units came back, which is 2.17% of units sold.
What I would do next
Stock up and promote ahead of December, and plan for the January dip instead of being surprised by it.
Push accessories alongside bike orders. Their margin is 22 points higher than bikes and they already sell in volume.
Look into why the Central, Northeast and Southeast US regions sell almost nothing, since the product clearly sells in the Southwest and Northwest.
Review jersey pricing or cost, as their margin is well below everything else.
Conclusion
Writing the measures taught me that most of the work in a KPI dashboard is deciding what each number means. Sales had to be built from quantity and price, an order and an order line are not the same thing, and a growth percentage on a half year is easy to misread.
Checking the same values in DAX Studio and on the cards made me trust them. The result is a set of 27 measures, a dashboard that answers the six questions in the brief, and a clear picture of a business that grows fast, depends on bikes, and peaks every December.
Top comments (0)