Lab · Hands-on Exercise
Q1 Sales Report
A comprehensive end-to-end lab — two tables, DAX calculated columns, Power Query transformations, three report pages, a tooltip page, and variable DAX measures.
You are employed as a data analyst with the Retail Department.
You have assembled important sales information which you would like all your department staff
to be aware of. The data is currently in an Excel file — your goal is to build a Power BI
report analysing sales broken down by branch and product line.
Setup — Load the Data
This file contains two tables. Make sure to select the table objects, not the sheet objects, in the Navigator window.
Get Data → Excel Workbook. Navigate to and open
Q1-Sales.xlsx.
In the Navigator, select both Q1Sales and Itemlist
— choose the table objects (with the table icon), not the sheet objects.
Click Load.
Verify the Date column is formatted as a Date type
in Column Tools.
DAX — Calculated Columns
Create these calculated columns in the Q1Sales table before building any visuals.
Create a calculated column named total — multiply
Unit Price by Quantity.
total = Q1Sales[Unit Price] * Q1Sales[Quantity]
Create a calculated column named profit — subtract
COGs from total.
profit = Q1Sales[total] - Q1Sales[COGs]
Create a DAX calendar table using
Optional
CALENDARAUTO(),
name the table calendar.
calendar = CALENDARAUTO()
Power Query — Add Columns
Open Transform Data and work on the Q1Sales query. Use Add Column → Date for the date extractions.
Add a column that displays the Week of the Year
— keep the default column name Power Query assigns.
Add Column → Date → Week → Week of Year
Add Column → Date → Week → Week of Year
Add a column that displays the Name of the Day.
Add Column → Date → Day → Name of Day
Add Column → Date → Day → Name of Day
Delete the
Time column — it is not needed in the model.
Use Add Column (not Transform) for the date extractions — the original
Date column must remain intact for relationships and time intelligence.
1
Formatting
Column Tools ribbon — number formats
Format Quantity with a comma separator.
Format Unit Price with a comma separator.
Format Total with a $ currency symbol and no decimal places.
2
Data Modeling
Relationships between tables
Create a 1 : M relationship between Itemlist
and Q1Sales — join on
Itemlist[Item no] →
Q1Sales[Item].
Create a 1 : M relationship between calendar
and Q1Sales — join on
Optional
calendar[Date] →
Q1Sales[Date].
3
Report Page — Gender
Buying behaviour broken down by gender
Slicer — filter by gender.
Place it top left.
Card — display Sum of Total.
Place it under the slicer.
Bar Chart — total sales by product category.
Hide the chart title. Place it bottom left.
Line Chart — trend of total sales over weeks.
Place it bottom right, next to the bar chart.
Table — list the top 3 products by total
(apply a Top N filter). Hide the total row from the table.
Place it under the card.
Table — Product Category with
Sum of Total, Average of Total, Max of Total, Min of Total.
Place it top right, above the line chart.
4
Report Page — Variance
Month-over-month comparison with dynamic DAX text
Table — Month, Sum of Total,
Previous Month Sales, and Change.
See DAX Reference below for the
previous month measure.
Line Chart — trend of total sales over months.
Place it bottom right, next to the table.
Pie Chart — total sales by gender.
Place it above the table.
Two Slicers — one by City, one by Branch.
Place both top left. Set both to Dropdown style.
Card — display the
read DAX measure (dynamic text).
Reads "you are viewing data from [city] related to [branch]".
Defaults to Qatar if no city selected, all branches if no branch selected.
See DAX Reference below.
Column Chart — total sales by Branch.
5
Report Page — Tooltip
Custom tooltip page assigned to the variance line chart
In Page information (Format pane), enable
Allow use as tooltip → On.
Table — two columns: City and Sum of Total.
Column Chart — Product Category and Sum of Total.
Card — displays the
selected month DAX measure.
Shows the month name if a single month is selected, otherwise shows
"All Q1 Sales". See DAX Reference below.
Adjust the page size in Format → Page size to fit the tooltip layout.
Go to the variance page, select the line chart, and assign
this page as its tooltip in the Visualizations pane.
6
Formatting
Borders, spacing, and visual polish
Add borders around each visualization on all pages.
Space all visuals in a balanced, aligned layout.
Apply any additional colour, font, or title formatting to improve the report.
7
Save
Name and save the file
Save the Power BI file as sales report.
DAX Reference
Three measures used across Tasks 4 and 5. Create them before building those report pages.
read
Task 4 · Dynamic Text
read =
VAR selectedcity = IF(HASONEVALUE(Q1Sales[City]), VALUES(Q1Sales[City]), "Qatar")
VAR selectedbranch = IF(HASONEVALUE(Q1Sales[Branch]), VALUES(Q1Sales[Branch]), " all branches")
RETURN
"you are viewing data from " & selectedcity & " related to " & selectedbranch
VAR selectedcity = IF(HASONEVALUE(Q1Sales[City]), VALUES(Q1Sales[City]), "Qatar")
VAR selectedbranch = IF(HASONEVALUE(Q1Sales[Branch]), VALUES(Q1Sales[Branch]), " all branches")
RETURN
"you are viewing data from " & selectedcity & " related to " & selectedbranch
selected month
Task 5 · Tooltip Card
selected month = IF(
HASONEVALUE('calendar'[Date].[Month]),
VALUES('calendar'[Date].[Month]),
"All Q1 Sales"
)
HASONEVALUE('calendar'[Date].[Month]),
VALUES('calendar'[Date].[Month]),
"All Q1 Sales"
)
previous month
Task 4 · Variance Table
previous month = CALCULATE(
SUM(Q1Sales[total]),
DATEADD('calendar'[Date].[Date], -1, MONTH)
)
SUM(Q1Sales[total]),
DATEADD('calendar'[Date].[Date], -1, MONTH)
)