Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants an interactive sales report where they can see overall sales by year, then drill down to quarters and months. Also, they want to click on a specific month to see detailed sales transactions for that month.

📊 Data: You have sales data with columns: Date, Region, Product, Sales Amount, and Transaction ID.

🎯 Deliverable: Create a Power BI report with a sales trend chart that supports drill-down by year, quarter, and month, and a drill-through page showing detailed transactions for the selected month.

Progress0 / 7 steps

Sample Data

Date	Region	Product	Sales Amount	Transaction ID
2023-01-15	North	Widget A	120	T001
2023-01-20	South	Widget B	200	T002
2023-02-10	East	Widget A	150	T003
2023-03-05	West	Widget C	300	T004
2023-04-12	North	Widget B	250	T005
2023-05-18	South	Widget A	180	T006
2023-06-22	East	Widget C	220	T007
2023-07-30	West	Widget B	270	T008
2023-08-15	North	Widget A	130	T009
2023-09-10	South	Widget C	310	T010
2023-10-05	East	Widget B	190	T011
2023-11-20	West	Widget A	160	T012

1

Step 1: Load the sales data into Power BI Desktop.

Use 'Get Data' > 'Excel/CSV' or connect to your data source and load the table with columns Date, Region, Product, Sales Amount, Transaction ID.

Expected Result

2

Step 2: Create a Date hierarchy for drill-down.

In the Fields pane, right-click the Date column and select 'New hierarchy'. Add Year, Quarter, and Month as levels in this hierarchy.

Expected Result

3

Step 3: Create a clustered column chart to show total sales by Date hierarchy.

Add a clustered column chart visual. Drag the Date hierarchy to the Axis field. Drag Sales Amount to the Values field (aggregated as SUM).

Expected Result

4

Step 4: Enable drill-down on the chart.

Click the 'Drill Down' button (down arrow icon) on the chart toolbar to enable drill-down mode.

Expected Result

5

Step 5: Create a drill-through page for detailed transactions.

Add a new report page named 'Transaction Details'. Add a table visual with columns: Date, Region, Product, Sales Amount, Transaction ID. Add Date (Month) as a Drill-through field in the Filters pane.

Expected Result

6

Step 6: Set up drill-through from the main chart to the details page.

On the main chart page, right-click a Month bar and select 'Drill through' > 'Transaction Details'.

Expected Result

7

Step 7: Test the report interaction.

Use the drill-down arrows on the chart to explore Year > Quarter > Month. Right-click a Month and drill through to see detailed transactions.

Expected Result

Final Result

Sales Trend Chart (Clustered Column)
Year 2023: ██████████████████ 2500
  ↓ Drill down
Quarter Q1: ████████ 770
Quarter Q2: ████████ 650
Quarter Q3: ████████ 710
Quarter Q4: ██████ 370
  ↓ Drill down
Month Jan: ████ 320
Month Feb: ██ 150
Month Mar: ████ 300
...

[Right-click Month bar] -> Drill-through -> Transaction Details Page

Transaction Details Table:
Date       | Region | Product  | Sales Amount | Transaction ID
2023-01-15 | North  | Widget A | 120          | T001
2023-01-20 | South  | Widget B | 200          | T002
...

✓Sales trend can be explored from Year to Quarter to Month easily.

✓Drill-through page shows detailed transactions for any selected month.

✓This interactive report helps managers understand sales patterns and details quickly.

Bonus Challenge

Add a slicer to filter sales by Region and see the drill-down and drill-through update accordingly.

Show Hint