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.