Create Stacked Funnel Chart in Power BI

Do you want to build a stacked funnel chart for your Power BI report? In this Power BI tutorial, I will guide you on how to create Stacked funnel chart in Power BI.

Unfortunately, the Power BI funnel chart does not support the creation of a stacked funnel chart. This limitation arises from the fact that the Power BI funnel chart does not allow for multiple categories.

As a result, each bar in the funnel chart is represented by a single color, as shown in the left image. However, our goal is to achieve a stacked funnel chart, as shown in the right image.

power bi stacked funnel visualization

To create a Stacked funnel chart, we can either utilize custom visuals or do a workaround by creating a Stacked bar chart.

For example, we will use the below Recruitment data, to create a funnel chart in Power BI.

Stacked funnel chart in Power BI

How to create a Stacked Funnel chart in Power BI

To create a stacked funnel chart, we will follow the below steps:

  • Transform the table into a pivot table
  • Create a calculated column and measure
  • Create a Stacked bar chart
  • Format the Stacked bar chart

Transform the table into a pivot table

  • Log in to the Power BI Desktop, then click on Excel Workbook and select Excel from your local system.
  • The Navigator window will open, select the table and click on the Transform button.
Stacked funnel chart in Power BI example

Now, the Power Query editor will open and select the region column from the table.

See also  Power bi slicer panel - How to use

Then go to the Transform tab, select the Unpivot columns dropdown, and then select Unpivot other columns.

Stacked funnel chart Power BI example

Now you can see the below table, and rename the Attribute to Phases.

funnel in power bi
  • Then select the region column, and in the Transform tab, click on the Pivot
bi funnel

Now you can see the Pivot window will open. Under the Values column, select the Value from the dropdown. Then click on OK.

stacked funnel chart

Now you can see the pivoted table look like below, and then click on Apply and Load button, to load the changes to Power BI.

stacked funnel in power bi

Create a calculated column and measure

  • Now we will create a calculated column, that will calculate the total of each phase, for this, click on the Modeling tab -> then click on the New column.
  • Then write the below formula in the Formula box and you can see the below Total column is added to the table.
Total = interviewphase[North America]+ interviewphase[Los Angeles] +
 interviewphase[New York] +interviewphase[Chicago] + interviewphase[Middle East]
stacked funnel power bi
  • We will create a calculated column that will add the index based on the Phase so we can order the phases in the visual.
  • Then, in the formula box, write the below formula, and you can see the Phase Order Number column.
Phase Order = if (interviewphase[Phases]= "Application", 1, if(interviewphase[Phases]= "Screening", 2, if(interviewphase[Phases]= "Interview", 3, if(interviewphase[Phases]= "Assessment", 4, if(interviewphase[Phases]= "Offer", 5,0))))) 
stacked funnel visual power bi

Next, we will calculate a reminder; for this, click on the New Measure, and then write the below formula in the formula box:

City = 
VAR longest_bar = CALCULATE ( MAX (interviewphase[Total] ), ALL ( interviewphase ) )
RETURN(longest_bar - MAX(interviewphase[Total])) /2
funnel charts in power bi

Create a Stacked bar chart

  • Select the Stacked bar chart from the visualization pane in Power BI Desktop.
  • You can see the blank stacked bar chart is added to the Canvas.
stacked funnel charts in Power BI
  • Then drag and drop the Phases to the Y-axis, City, Chicago, Los Angeles, Middle East, New York, and North America to the X-axis, and then Phase Order to the Tooltip.
stacked funnel charts Power BI

Next, we will arrange the Phases in Chronological order; for this, click on the (…) more option.

See also  How To Use Power Apps Image Control [Detailed Guide]

Then select the Sort axis -> select the Sum of Phase Order and then select ‘Sort ascending.

stacked funnel visualization Power BI

Now you can see the phases are in order in the Stacked bar chart.

stacked funnel visualization in Power BI

Format the Stacked bar chart

  • Format the Stacked bar chart so that it will look like a stacked funnel chart.
  • Select the visual, then click on the Format icon in the Visualization pane.
  • Then, enable the Data label option.
Power BI stacked funnel chart
  • Next, expand the Bar section, then under Series select the city.
  • Change the color to white for that series.
power bi funnel visual

Now you can see the Stacked funnel chart in Power BI.

power bi stacked funnel visual

This is how we can create a Stacked funnel chart in Power BI.

Also, you may like:

Conclusion

In this Power BI tutorial, we saw how to create a Stacked funnel chart, without using the custom visuals in Power BI.

>