Drill-down and drill-through in Power BI - Real Business Scenario

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.

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

Sales data is loaded and visible in the Fields pane.

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

A Date hierarchy with Year > Quarter > Month is created.

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

Chart shows total sales by Year initially.

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

Chart allows clicking on a Year bar to drill down to Quarters, then Months.

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

Drill-through page is ready to show details filtered by selected Month.

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

Report navigates to the details page showing transactions for the selected Month.

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

Interactive report with drill-down and drill-through works as expected.

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

Add a slicer visual with the Region field. Select a region to filter all visuals on the page.

Practice

1. What is the main difference between drill-down and drill-through in Power BI?

easy

A. Drill-down explores data levels within the same visual, drill-through navigates to another page.

B. Drill-down creates new visuals, drill-through deletes existing visuals.

C. Drill-down filters the entire report, drill-through filters only one visual.

D. Drill-down exports data, drill-through imports data.

Solution

1. Step 1: Understand drill-down functionality

   Drill-down lets you explore different levels of data hierarchy inside the same visual, like going from year to month.

2. Step 2: Understand drill-through functionality

   Drill-through takes you to a different report page filtered by the selected data point, focusing on details.

3. Final Answer:

   Drill-down explores data levels within the same visual, drill-through navigates to another page. -> Option A

4. Quick Check:

   Drill-down = same visual levels, Drill-through = different page [OK]

Hint: Drill-down stays on page; drill-through jumps to another page [OK]

Common Mistakes:

• Confusing drill-down with drill-through navigation
• Thinking drill-down changes pages
• Assuming drill-through modifies the same visual

2. Which of the following is the correct way to enable drill-through on a report page in Power BI?

easy

A. Use the 'Export Data' option on the source visual.

B. Create a new visual and enable drill-down in its format settings.

C. Add a drill-through filter field to the Drill-through filters pane on the target page.

D. Add a slicer to the source page and link it to the target page.

Solution

1. Step 1: Identify drill-through setup

   To enable drill-through, you must add a field to the Drill-through filters pane on the target page to receive filter context.

2. Step 2: Check other options

   Options B, C, and D do not correctly enable drill-through functionality.

3. Final Answer:

   Add a drill-through filter field to the Drill-through filters pane on the target page. -> Option C

4. Quick Check:

   Drill-through filter field setup = Add a drill-through filter field to the Drill-through filters pane on the target page. [OK]

Hint: Drill-through needs filter field on target page filters pane [OK]

Common Mistakes:

• Trying to enable drill-through on source page instead of target
• Confusing drill-down format settings with drill-through setup
• Using slicers instead of drill-through filters

3. Given a bar chart with Year > Quarter > Month hierarchy enabled for drill-down, what happens when you click the drill-down button and then select a year bar?

medium

A. The report navigates to a new page filtered by the selected year.

B. The chart shows quarters for the selected year within the same visual.

C. The chart exports data for the selected year.

D. Nothing happens because drill-down is disabled.

Solution

1. Step 1: Understand drill-down behavior on hierarchy

   Clicking drill-down and selecting a year drills into the next level, showing quarters for that year in the same visual.

2. Step 2: Eliminate other options

   The report navigates to a new page filtered by the selected year. describes drill-through, not drill-down. The chart exports data for the selected year. is unrelated. Nothing happens because drill-down is disabled. contradicts the question setup.

3. Final Answer:

   The chart shows quarters for the selected year within the same visual. -> Option B

4. Quick Check:

   Drill-down moves down hierarchy in same visual [OK]

Hint: Drill-down moves inside visual hierarchy levels [OK]

Common Mistakes:

• Confusing drill-down with drill-through navigation
• Expecting page change on drill-down
• Ignoring hierarchy levels in drill-down

4. You set up a drill-through page but clicking the drill-through option on the source visual does nothing. What is the most likely cause?

medium

A. The drill-through filter field is missing on the target page.

B. The source visual does not have any data points.

C. The drill-down button is enabled instead of drill-through.

D. The report page is not published.

Solution

1. Step 1: Check drill-through setup requirements

   Drill-through requires a filter field on the target page to receive context from the source visual.

2. Step 2: Analyze other options

   The source visual does not have any data points. might affect visuals but not drill-through availability. The drill-down button is enabled instead of drill-through. confuses drill-down with drill-through. The report page is not published. is unrelated to drill-through functionality.

3. Final Answer:

   The drill-through filter field is missing on the target page. -> Option A

4. Quick Check:

   Missing drill-through filter field blocks drill-through [OK]

Hint: Always add drill-through filter field on target page [OK]

Common Mistakes:

• Confusing drill-down and drill-through buttons
• Not adding filter field on target page
• Assuming drill-through works without data

5. You want to create a report where users can drill-down from Region to Country to City in a map visual, and also drill-through to a detailed sales page filtered by City. Which setup is correct?

hard

A. Create separate visuals for each level and link them with bookmarks.

B. Add City as a slicer on the main page and enable drill-down on the detailed page.

C. Use drill-through filters on the map visual and disable drill-down.

D. Enable hierarchy drill-down on the map visual and add City as a drill-through filter on the detailed page.

Solution

1. Step 1: Setup drill-down hierarchy on map visual

   Enable hierarchy drill-down from Region to Country to City so users can explore levels inside the same visual.

2. Step 2: Setup drill-through filter on detailed page

   Add City as a drill-through filter on the detailed sales page to allow navigation filtered by City.

3. Step 3: Confirm other options are incorrect

   Add City as a slicer on the main page and enable drill-down on the detailed page. misplaces drill-down on detailed page. Use drill-through filters on the map visual and disable drill-down. disables drill-down incorrectly. Create separate visuals for each level and link them with bookmarks. uses bookmarks, which is not drill-down or drill-through.

4. Final Answer:

   Enable hierarchy drill-down on the map visual and add City as a drill-through filter on the detailed page. -> Option D

5. Quick Check:

   Drill-down + drill-through setup = Enable hierarchy drill-down on the map visual and add City as a drill-through filter on the detailed page. [OK]

Hint: Combine drill-down hierarchy and drill-through filter for best UX [OK]

Common Mistakes:

• Confusing where to enable drill-down vs drill-through
• Not adding drill-through filter on target page
• Using bookmarks instead of drill-through