Power BI Blog: Funnel Chart – Part 2

Welcome back to this week’s edition of the Power BI blog series.  Last week, we looked at creating a basic Funnel chart.  This week, we will show you some workarounds to customise it a little more.

Adding Custom Labels

Let’s say we wanted a little more detail on our Funnel chart.  We’re now going to be taking data from a file called ‘Sales_Funnel’ that can be found here.  Once this data is loaded, we want to use it to create a sales funnel with both the number and percentage of leads showcased on the chart.  We need to start by creating individual summaries for each segment type: Cold Calling, Digital and Roadshows.

Create a measure for all three [3] structured like the one above.  Once these are created, we will create a measure called Opportunities which will sum these measures together to produce the total number of opportunities.

Next, we want to create another measure called Bar Offset.  This measure will be a bit more complicated and will involve using variables.

This is a complex measure so I’ll come back to explain it once we can see how it’s being used.  The next step is to create a normal stacked bar chart.  Set the y-axis as Stage and have Bar Offset and Opportunities set as the x-axis.  

Now we can see visually how the Bar Offset measure is working.  The vMaxValue variable finds the maximum number of opportunities available across all stages (which is also equal to the size of Opportunities in the first stage).  The final calculation takes this maximum value and subtracts the number of opportunities divided by two [2].  This means that at each stage as the value for opportunities decreases, the output of the measure increases proportionally.

Now there are a few things we may do to make it look slightly nicer under Format visual.  We can start by switching Ribbons and Data labels to On.  Under Ribbons -> Apply settings to -> Series -> Bar Offset set the transparency to 100%.  Meanwhile for Opportunities set the transparency to 30% or whatever you think looks good.

Now we’re going to create our custom labels.  We first need to make two more measures.  The first will calculate the percentage conversion to the selected stage from the original prospection stage.

The second will instead calculate the percentage conversion to the selected stage from the stage before it.

Now that these are set up, we can click back onto our visual and go to Format visual -> Data labels -> Apply settings to -> Bar Offset.  Under Value -> Field select percentage of First Value and below under Detail -> Data select percentage of Previous Stage.  Under Layoutselect Multi-line to put the different labels on two separate lines.  Now we need to do the same thing for Apply settings to -> Opportunities, except filling Value -> Field with Opportunities and Detail -> Data with Stage.  From here you can format the colours however you’d like but should be left with a Funnel chart that looks something like this.

Breaking it into Segments

What if we wanted to split our funnel into the different sources to look at how effective each is at generating opportunities?  We can actually use the same measures that we’ve already created.  The only thing to do is to select the visual and change the measures in the x-axis.  While we will leave the Bar Offset measure in there, we can replace the Opportunities measure with Cold Calling, Digital, and Roadshows.  This should result in a chart that looks something like this:

The formatting of the labels may be adjusted the same way as in the previous example.  All we did here was remove the “detail” label for Cold Calling, Roadshows and Digital, and scale down the size of the numbers a bit to fit better.

That’s it for the Funnel chart; let us know if you have any other useful modifications for it. 

Tune in next week for another Power BI blog.  In the meantime, if you wish to catch up on past articles you can find all of our past Power BI blogs here.