For my first #WOW2023 challenge, as an ‘official’ part of the #WOW crew, I challenged everyone to build this funnel chart. Inspiration came from a colleague who posed me the challenge of building something similar, ensuring the bars were a single entity when clicked on. In that example we’d just used Superstore data, but it wasn’t really reflective of how a funnel would typically work, so I sourced some dummy sales opportunity data and made the challenge more relevant to a real business scenario.

Building the Funnel Chart

I saw this as a table calculation exercise, though I have subsequently seen solutions that manage it all through LODs. I’ll obviously blog my solution.

Let’s start by getting the core data into a table, so we can see what we’re aiming for

The total value of all the Opportunities is $91.4M. Based on the sales pipeline process and the assumptions documented in the requirements, all of that value will have passed through the first stage – Prospecting.

There is $6.1M still sitting in Prospecting, so when we move onto the next stage, stage 2 -Qualification, the total amount that has passed through will be $91.4M – $6.1M = $85.3M

As there is $6.5M sitting in Qualification, as we move to stage 3, the total amount that has passed through will be $85.3M – $6.5M = $78.7M. A similar pattern repeats for Stage 4, but for Stage 5, Closed Won, the value that has ‘passed’ through that stage, is equivalent to itself.

If we amend the **Value** field to be a **Running Total quick table calculation** (and add **Value** back into the view too), we get

The Running Sum is computing down the table , with the value in each row being the sum of the preceding rows. But this is opposite to what we want. We want Stage 1 to have the biggest value, the total, and the values to get smaller as you look down the table.

To resolve this, edit the table calculation and change the **Sort order** to **custom sort** by **Minimum Stage No Descending**

This results in the running total to sum from the last row (Stage 6) up to the first row (Stage 1), and you can see the value against Stage 1 matches the total for the **Value** column.

However, this still isn’t quite what we need. While we need to consider the amount for Closed Lost in stages 1-4, we shouldn’t be including it in stage 5, Closed Won.

While excluding the Stage 6 from the view (**Stage No **on **Filter** shelf set to Exclude 6) , gives the correct value for Stage 5, we’ve now lost the amount for Closed Lost in the other rows

So, we need some additional calculations to help resolve this.

**Amount Lost**

{FIXED : SUM(IF [Stage No]=6 THEN [Value] END)}

This just captures the amount of Stage 6 and ‘spreads it across every row of data.

Let’s now get our running total field, baked into it’s own calculation, as this will help with other calcs

**Cumulative Value Per Stage**

RUNNING_SUM(SUM([Value]))

Replace the **Value** table calc field with this one, ensuring the table calculation settings are identical to those described above.

Now let’s add the **Amount Lost** onto the **Cumulative Value Per Stage** unless we’re at Stage 5, Closed Won

**Total Amount Per Stage Inc Lost**

IF MIN([Stage No]) = 5 THEN [Cumulative Value Per Stage]

ELSE [Cumulative Value Per Stage] + SUM([Amount Lost]) END

Pop this into the table (checking the table calc settings are as expected) and the total amount now stored against Stage 1, is the total value of all the Opportunities.

Ok, so now we know the values, how do we plot this in such a way that it represents a funnel. I chose to do this by first working out the proportion of the total value that the ‘reverse’ cumulative value represented.

**Total Value**

{FIXED:SUM([Value])}

meant I could determine

**Proportion of Total**

[Total Amount Per Stage Inc Lost] / SUM([Total Value])

I formatted this to percentage with 0 dp.

Adding these into the table

If we plotted this information on a bar chart, we’d get this

but we need to ‘centre’ each bar in relation to the first bar to give us the funnel effect. This means we need to shift each ‘bar’ to the right, by half the amount that is the difference between 100% and the **Proportion of Total.**

**Position to Plot**

(1 – [Proportion of Total])/2

We can’t represent this with a actual bar chart, but instead we can use a Gannt chart.

**Stage No**to**Rows****Stage No**to**Filter**and exclude Stage 6**Stage**to**Detail****Position to Plot**to**Columns**, adjusting the table calculation as previously described- Change mark type to
**Gantt bar** - Add
**Proportion of Total**to**Size**(and verify the table calc is set properly)

Hey presto! A funnel!

To finalise

- add
**Stage**to**Label**and align centrally. Make the font bold and**match mark colour**. - add
**Cumulative Value per Stage**to**Colour**and reverse the colour range (via the**Edit Colours**dialog) - Widen each row a bit.
- Fix the
**Position to Plot**axis to start at 0 and end at 1 to ensure the funnel is centred exactly. - Add
**Value**and**Total Amount Per Stage Inc Lost**to the**Tooltip**and adjust the tooltip text accordingly (don’t forget to sense check the table calc settings!). - Hide the
**Position to Plot**axis, and the**Stage N**o column. Remove all gridlines, zero lines, axis rulers /row/column banding etc.

Building the KPIs

We need a few calculated fields to store the required numbers

**Won**

{FIXED:SUM(IF [Stage No]=5 THEN [Value] END)} / [Total Value]

formatted to a percentage with 0 dp.

**Lost**

{FIXED:SUM(IF [Stage No]=6 THEN [Value] END)} / [Total Value]

formatted to a percentage with 0 dp.

**Outstanding**

1-([Lost] + [Won])

formatted to a percentage with 0dp.

On a new sheet add **Measure Names** to **Columns** and **Measure Values** to **Text.** Add **Measure Names** to **Filter** and select **Total Value, Won, Lost, Outstanding.** Manually re-order the columns.

Format **Total Value** to be a number displayed in millions with 1 decimal place & prefixed with $.

Add **Measure Names** to **Text** and adjust the text as required. Align the text to be centred.

Remove the row banding, and hide the column heading.

Then arrange the two sheets on the dashboard, ensuring both are set to **fit entire view**.

My published viz is here.

Hope you enjoyed this

Happy vizzin’!

*Donna*