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.
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.
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.
Now, the Power Query editor will open and select the region column from the table.
Then go to the Transform tab, select the Unpivot columns dropdown, and then select Unpivot other columns.
Now you can see the below table, and rename the Attribute to Phases.
- Then select the region column, and in the Transform tab, click on the Pivot
Now you can see the Pivot window will open. Under the Values column, select the Value from the dropdown. Then click on OK.
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.
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]
- 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)))))
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
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.
- 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.
Next, we will arrange the Phases in Chronological order; for this, click on the (…) more option.
Then select the Sort axis -> select the Sum of Phase Order and then select ‘Sort ascending.
Now you can see the phases are in order in the Stacked bar chart.
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.
- Next, expand the Bar section, then under Series select the city.
- Change the color to white for that series.
Now you can see the Stacked funnel chart in Power BI.
This is how we can create a Stacked funnel chart in Power BI.
Also, you may like:
- Clustered Column Chart in Power BI
- Horizontal Funnel Chart in Power BI
- Power BI Waterfall Chart
- How to Apply Transparency in Power BI Area Chart
- Pyramid Chart In Power BI
- Power BI Scheduled Refresh
Conclusion
In this Power BI tutorial, we saw how to create a Stacked funnel chart, without using the custom visuals in Power BI.
After working for more than 15 years in Microsoft technologies like SharePoint, Office 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (9 times). I have also worked in companies like HP, TCS, KPIT, etc.