Funnel charts in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ visualization

intermediate

Identify the correct funnel chart setup

You want to create a funnel chart in Power BI to show the sales process stages: Leads, Qualified, Proposal, and Closed. Which setup will correctly display the funnel stages in descending order of count?

A Use 'Stage' as Group and 'Count of Customers' as Values, sorted ascending by 'Stage'.

B Use 'Count of Customers' as Group and 'Stage' as Values, sorted ascending by 'Stage'.

C Use 'Stage' as Values and 'Count of Customers' as Group, no sorting applied.

D Use 'Stage' as the Group and 'Count of Customers' as the Values, sorted descending by 'Count of Customers'.

💡 Hint

Remember, funnel charts group by categories and measure the size by values, sorted to show the largest stage first.

In funnel charts, the 'Group' field defines the stages/categories, and the 'Values' field defines the size of each stage. Sorting descending by the value ensures the funnel narrows correctly.

❓ dax_lod_result

intermediate

Calculate conversion rate between funnel stages

Given a table 'SalesStages' with columns 'Stage' and 'CustomerCount', write a DAX measure to calculate the conversion rate from 'Leads' to 'Closed'. What is the correct measure?

A Conversion Rate = DIVIDE(CALCULATE(SUM(SalesStages[CustomerCount]), SalesStages[Stage] = "Closed"), CALCULATE(SUM(SalesStages[CustomerCount]), SalesStages[Stage] = "Leads"))

B Conversion Rate = SUM(SalesStages[CustomerCount]) / CALCULATE(SUM(SalesStages[CustomerCount]), SalesStages[Stage] = "Leads")

C Conversion Rate = CALCULATE(SUM(SalesStages[CustomerCount]), SalesStages[Stage] = "Leads") / CALCULATE(SUM(SalesStages[CustomerCount]), SalesStages[Stage] = "Closed")

D Conversion Rate = DIVIDE(CALCULATE(SUM(SalesStages[CustomerCount]), SalesStages[Stage] = "Leads"), CALCULATE(SUM(SalesStages[CustomerCount]), SalesStages[Stage] = "Closed"))

💡 Hint

Conversion rate is the count at the last stage divided by the count at the first stage.

The measure divides the sum of customers at the 'Closed' stage by the sum at the 'Leads' stage using CALCULATE and DIVIDE to avoid errors.

❓ data_modeling

advanced

Modeling data for funnel chart with multiple funnels

You have sales data for two products, each with stages: Leads, Qualified, Proposal, Closed. How should you model your data to create a funnel chart that compares both products side by side?

A Create a single table with columns: Product, Stage, CustomerCount, then use Product as Small multiples and Stage as Group.

B Create separate tables for each product with Stage and CustomerCount, then merge them in the funnel chart.

C Create a table with Stage and CustomerCount only, ignoring Product, then filter manually.

D Create a table with Product as rows and Stage as columns, then use a matrix visual instead of funnel chart.

💡 Hint

Think about how to compare multiple categories side-by-side in a funnel chart.

A single table with Product and Stage columns allows using Product as Small multiples and grouping by Stage in the funnel chart, enabling side-by-side comparison.

🔧 Formula Fix

advanced

Troubleshoot funnel chart showing incorrect stage order

Your funnel chart shows stages in alphabetical order instead of the business process order: Leads, Qualified, Proposal, Closed. What is the most likely cause?

A The data model has duplicate stage names causing confusion in sorting.

B The funnel chart does not support custom sorting and always sorts alphabetically.

C The 'Stage' column is sorted alphabetically because it is a text field without a sort order defined.

D The 'CustomerCount' measure is not aggregated correctly, causing sorting issues.

💡 Hint

Check how text columns are sorted in Power BI visuals.

Text columns sort alphabetically by default. To fix, create a numeric sort column representing stage order and set 'Stage' to sort by that column.

🧠 Conceptual

expert

Explain the limitation of funnel charts in Power BI

Which of the following is a known limitation of funnel charts in Power BI that affects how data can be visualized?

A Funnel charts automatically aggregate data by date, which cannot be changed.

B Funnel charts only support one measure and one category field, limiting multi-metric comparisons.

C Funnel charts allow unlimited drill-down levels, which can confuse users.

D Funnel charts support conditional formatting on each stage independently.

💡 Hint

Think about how many measures and categories funnel charts accept.

Power BI funnel charts accept only one category and one measure, so you cannot compare multiple metrics in the same funnel chart directly.

Practice

1. What is the main purpose of a Funnel chart in Power BI?

easy

A. To show the relationship between two numeric variables

B. To display geographic data on a map

C. To compare sales across different regions using bars

D. To show how many items move through ordered steps in a process

Solution

1. Step 1: Understand the funnel chart concept

   A funnel chart visualizes stages in a process and how many items pass through each stage.

2. Step 2: Match the purpose with options

   Only To show how many items move through ordered steps in a process describes showing items moving through ordered steps, which is the funnel chart's purpose.

3. Final Answer:

   To show how many items move through ordered steps in a process -> Option D

4. Quick Check:

   Funnel chart = ordered steps count [OK]

Hint: Funnel charts track step-by-step drop-offs [OK]

Common Mistakes:

• Confusing funnel charts with bar or map charts
• Thinking funnel charts show relationships between variables
• Assuming funnel charts display geographic data

2. Which of the following is the correct way to set up a funnel chart in Power BI?

easy

A. Use a numeric field for steps and a category field for counts

B. Use a category field for steps and a numeric field for counts

C. Use two numeric fields, one for steps and one for counts

D. Use two category fields, one for steps and one for counts

Solution

1. Step 1: Identify the data roles in funnel charts

   Funnel charts require a category field to define the ordered steps and a numeric field to show counts or amounts.

2. Step 2: Match the correct data setup

   Use a category field for steps and a numeric field for counts correctly assigns category for steps and numeric for counts, which is the required setup.

3. Final Answer:

   Use a category field for steps and a numeric field for counts -> Option B

4. Quick Check:

   Category = steps, Numeric = counts [OK]

Hint: Steps = category, counts = numeric [OK]

Common Mistakes:

• Swapping numeric and category fields
• Using two numeric fields instead of category + numeric
• Using two category fields which cannot show counts

3. Given the following data for a funnel chart:
Step: [Visited Website, Added to Cart, Purchased]
Count: [1000, 300, 150]
What will the funnel chart show at the 'Added to Cart' step?

medium

A. 300

B. 450

C. 150

D. 1000

Solution

1. Step 1: Identify the count for the 'Added to Cart' step

   The count given for 'Added to Cart' is 300.

2. Step 2: Understand funnel chart display

   The funnel chart shows the count value for each step, so at 'Added to Cart' it will show 300.

3. Final Answer:

   300 -> Option A

4. Quick Check:

   Count at 'Added to Cart' = 300 [OK]

Hint: Match step name to count value [OK]

Common Mistakes:

• Choosing the first step's count instead of the current step
• Adding counts instead of reading the step's count
• Confusing 'Purchased' count with 'Added to Cart'

4. You created a funnel chart but it shows all steps with the same size bars. What is the most likely cause?

medium

A. You used a measure that returns the same value for all steps

B. You used a category field for counts instead of a numeric field

C. You did not sort the steps in the correct order

D. You used a numeric field for steps instead of a category field

Solution

1. Step 1: Analyze why all bars are the same size

   If all bars have the same size, the numeric values used for counts are likely identical for every step.

2. Step 2: Identify the cause in data or measure

   A measure returning the same value for all steps causes this issue, as the funnel chart uses those values for bar sizes.

3. Final Answer:

   You used a measure that returns the same value for all steps -> Option A

4. Quick Check:

   Same value measure = equal bar sizes [OK]

Hint: Check measure values differ per step [OK]

Common Mistakes:

• Assuming sorting affects bar size
• Confusing category and numeric fields causing this issue
• Ignoring measure logic that returns constant values

5. You want to create a funnel chart showing customer drop-off through 4 steps: Visited, Signed Up, Subscribed, Renewed. Your data has counts for each step but the funnel chart shows steps in alphabetical order instead of the process order. How can you fix this?

hard

A. Use a pie chart instead of a funnel chart

B. Change the numeric counts to text values

C. Sort the category field by a numeric column that defines the step order

D. Remove the numeric field and use only the category field

Solution

1. Step 1: Understand the sorting issue in funnel charts

   By default, Power BI sorts category fields alphabetically, which can misorder process steps.

2. Step 2: Apply sorting by numeric order column

   Adding a numeric column that defines the correct step order and sorting the category field by it fixes the order in the funnel chart.

3. Final Answer:

   Sort the category field by a numeric column that defines the step order -> Option C

4. Quick Check:

   Sort steps by numeric order column [OK]

Hint: Sort steps by numeric order column [OK]

Common Mistakes:

• Trying to fix order by changing counts to text
• Switching chart type instead of fixing order
• Removing numeric field which breaks funnel chart