Funnel charts in Power BI - Deep Dive

Overview - Funnel charts

What is it?

A funnel chart is a type of visualization that shows a process with stages, where each stage has a smaller value than the previous one. It looks like a funnel or triangle, wide at the top and narrow at the bottom. This chart helps you see how many items or people move through each step and where they drop off. It is often used to track sales, marketing, or any step-by-step process.

Why it matters

Funnel charts exist to help businesses understand where they lose customers or opportunities in a process. Without funnel charts, it would be hard to spot the biggest drop-offs or bottlenecks quickly. This means companies might waste time and money fixing the wrong problems or miss chances to improve. Funnel charts make it easy to focus on the most important stages to improve.

Where it fits

Before learning funnel charts, you should know basic chart types like bar and column charts and understand simple data aggregation. After funnel charts, you can learn about conversion rate analysis, cohort analysis, and advanced process mining techniques.

Mental Model

Core Idea

A funnel chart visually shows how values shrink step-by-step through a process, highlighting where the biggest losses happen.

Think of it like...

Imagine pouring water through a funnel in your kitchen. The wide top holds a lot of water, but as it flows down, the funnel narrows, and less water passes through each smaller section. The funnel chart is like watching that water flow and seeing where it slows or leaks out.

Process Stages and Values
┌───────────────┐
│   Stage 1     │ 1000
│  ┌─────────┐  │
│  │ Stage 2 │ 800
│  │         │  │
│  └────┬────┘  │
│       │       │
│   Stage 3     │ 500
│  ┌────┴────┐  │
│  │ Stage 4 │ 200
│  └─────────┘  │
└───────────────┘
(Wide to narrow shape)

Build-Up - 6 Steps

1

Foundation

Understanding funnel chart basics

Concept: Learn what a funnel chart is and what it shows.

A funnel chart displays a sequence of stages in a process. Each stage has a value that usually decreases from the previous one. The chart looks like a funnel, wide at the top and narrow at the bottom. It helps you see how many items or people move through each step and where they drop off.

Result

You can identify the stages of a process and understand that funnel charts visualize shrinking values step-by-step.

Understanding the basic shape and purpose of funnel charts sets the foundation for using them to analyze processes.

2

Foundation

Creating a simple funnel chart in Power BI

Concept: Learn how to build a funnel chart using Power BI's built-in visual.

In Power BI Desktop, load your data with stages and values. Select the funnel chart visual from the Visualizations pane. Drag the stage field to the 'Group' area and the value field to the 'Values' area. Power BI automatically orders the stages and draws the funnel shape based on values.

Result

You get a funnel chart showing each stage's value as a shrinking section from top to bottom.

Knowing how to create a funnel chart in Power BI lets you quickly visualize stepwise data without complex setup.

3

Intermediate

Ordering and sorting funnel stages

🤔Before reading on: do you think funnel charts always sort stages alphabetically or by value? Commit to your answer.

Concept: Learn how to control the order of stages in a funnel chart for meaningful analysis.

Power BI may sort funnel stages alphabetically by default, which can confuse the process flow. To fix this, create a numeric sort order column in your data that defines the correct sequence of stages. Then, in Power BI, select the stage column and use 'Sort by Column' to apply the numeric order. This ensures the funnel flows logically from first to last stage.

Result

The funnel chart stages appear in the correct process order, making the visualization accurate and easy to understand.

Controlling stage order is crucial because a wrong sequence can mislead viewers about the process flow and drop-offs.

4

Intermediate

Interpreting funnel chart drop-offs

🤔Before reading on: do you think a small drop between stages always means a problem? Commit to your answer.

Concept: Learn how to read the funnel chart to find where the biggest losses happen and what they mean.

Look at the width difference between each stage in the funnel. A big drop means many items or people did not move to the next step. However, a small drop might be normal or expected. You should compare drops to business goals or benchmarks. Sometimes a drop is okay if the next stage is more selective or difficult.

Result

You can identify critical stages with large drop-offs and decide if they need attention or are normal.

Interpreting drop-offs carefully prevents false alarms and helps focus on real process issues.

5

Advanced

Using measures to customize funnel values

🤔Before reading on: do you think funnel charts can only use raw data columns for values? Commit to your answer.

Concept: Learn how to use DAX measures to calculate funnel values dynamically for more flexible analysis.

Instead of using a simple value column, create DAX measures that calculate counts or sums based on filters or conditions. For example, a measure can count only customers who meet certain criteria at each stage. Use these measures in the funnel chart's 'Values' field. This allows you to analyze funnels by segments, time periods, or other dimensions.

Result

The funnel chart updates dynamically based on your measure logic, enabling deeper insights.

Using measures unlocks powerful, customized funnel analysis beyond static data.

6

Expert

Limitations and alternatives to funnel charts

🤔Before reading on: do you think funnel charts always show accurate conversion rates? Commit to your answer.

Concept: Understand when funnel charts might mislead and what other visuals or methods to use instead.

Funnel charts assume a linear process with stages in order. If your process has loops, multiple paths, or non-linear flows, funnel charts can oversimplify or hide details. Also, funnel charts do not show time spent in stages or reasons for drop-offs. Alternatives include Sankey diagrams for complex flows, cohort analysis for time-based retention, or detailed tables with conversion rates. Use funnel charts only when the process is simple and linear.

Result

You avoid misusing funnel charts and choose better tools for complex process analysis.

Knowing funnel chart limits prevents wrong conclusions and encourages using the right visualization for the problem.

Under the Hood

Power BI funnel charts work by grouping data into stages and aggregating values for each stage. The visual then draws horizontal bars stacked vertically, each bar's width proportional to the stage value. The bars are arranged from largest (top) to smallest (bottom) to form a funnel shape. Internally, Power BI uses the data model and DAX engine to calculate these aggregations and applies sorting rules to order stages.

Why designed this way?

Funnel charts were designed to visually represent stepwise reduction in a process, making it easy to spot where losses occur. The funnel shape is intuitive because it matches the physical funnel concept, which naturally narrows. Power BI chose a simple bar-based funnel to keep rendering fast and compatible with its data model and filtering system. Alternatives like flow diagrams are more complex and less common.

Data Model
┌───────────────┐
│  Raw Data     │
│ (Stages, Val) │
└──────┬────────┘
       │
       ▼
Aggregation & Sorting
┌───────────────┐
│ DAX Measures  │
│ Sort by Order │
└──────┬────────┘
       │
       ▼
Funnel Visual
┌───────────────┐
│ Horizontal    │
│ Bars stacked  │
│ vertically    │
└───────────────┘

Myth Busters - 3 Common Misconceptions

Quick: do you think funnel charts always show the exact number of customers lost at each stage? Commit to yes or no.

Common Belief: Funnel charts show exact counts of lost customers at each stage.

Reality: Funnel charts show the remaining count at each stage, but not the exact number lost between stages unless you calculate differences separately.

Why it matters: Misunderstanding this can lead to wrong assumptions about how many customers drop off at each step, affecting decisions.

Quick: do you think funnel charts can handle processes with multiple paths easily? Commit to yes or no.

Common Belief: Funnel charts can clearly show complex processes with branches and loops.

Reality: Funnel charts are best for simple, linear processes. They cannot show multiple paths or loops well and may hide important details.

Why it matters: Using funnel charts for complex processes can mislead stakeholders and hide critical flow information.

Quick: do you think funnel charts automatically sort stages in the correct process order? Commit to yes or no.

Common Belief: Power BI funnel charts always sort stages in the right order automatically.

Reality: Power BI often sorts stages alphabetically by default, which may not match the process order. You must set a custom sort order.

Why it matters: Incorrect sorting confuses viewers and misrepresents the process flow.

Expert Zone

1

Funnel charts in Power BI do not support drill-down natively, so experts create linked visuals or use bookmarks for detailed stage analysis.

2

Using DAX measures for funnel values allows dynamic filtering and segmentation, but requires careful performance tuning on large datasets.

3

The visual's shape depends on relative values, so small absolute differences may look large if previous stages are small, which can mislead interpretation.

When NOT to use

Avoid funnel charts when your process has multiple branches, loops, or non-linear flows. Instead, use Sankey diagrams, process flow charts, or cohort analysis to capture complexity and time dynamics.

Production Patterns

Professionals use funnel charts to monitor sales pipelines, marketing campaign conversions, and onboarding processes. They combine funnel charts with slicers and filters to analyze segments and time periods. They also pair funnel charts with detailed tables or KPIs to explain drop-offs.

Connections

Conversion Rate Analysis

Builds-on

Understanding funnel charts helps grasp conversion rates between stages, which are key metrics in marketing and sales.

Sankey Diagrams

Alternative visualization

Knowing funnel chart limits leads to Sankey diagrams for complex flows, showing multiple paths and volumes between stages.

Supply Chain Management

Similar process flow concept

Supply chain stages and bottlenecks can be analyzed like funnel stages, showing how items move and where delays occur.

Common Pitfalls

#1 Stages appear in alphabetical order, not process order.

Wrong approach:

Using the stage name column directly without sorting:

Funnel chart Group: StageName
Values: Count

Correct approach:

Create a numeric sort column for stages:

StageOrder: 1, 2, 3...
Sort StageName by StageOrder

Then use StageName in funnel chart Group.

Root cause:

Not setting a custom sort order causes Power BI to default to alphabetical sorting.

#2 Using raw counts without filtering causes misleading funnel values.

Wrong approach:

Values: Count of all customers regardless of segment or date

Correct approach:

Create DAX measure filtering customers by segment or date:

Measure = CALCULATE(COUNT(CustomerID), FilterConditions)

Use measure in funnel chart Values.

Root cause:

Ignoring filtering needs leads to funnel values that do not reflect the intended analysis scope.

#3 Assuming funnel charts show exact drop-off numbers between stages.

Wrong approach:

Reading funnel widths as exact lost counts without calculation.

Correct approach:

Calculate drop-offs explicitly:

DropOff = [Stage N Value] - [Stage N+1 Value]

Use separate visuals or tables to show drop-offs.

Root cause:

Misunderstanding that funnel charts show remaining values, not differences.

Key Takeaways

Funnel charts visualize how values shrink through ordered stages in a process, highlighting where losses occur.

Correct stage ordering is essential for meaningful funnel charts; Power BI requires manual sorting setup.

Interpreting funnel charts requires understanding that drops may be normal or problematic depending on context.

Using DAX measures for funnel values enables dynamic, customized analysis beyond static counts.

Funnel charts are best for simple linear processes; complex flows need alternative visuals like Sankey diagrams.

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