One of the most common business metrics is a visual representation of how your deals move through the pipeline.
In this example we will work with Opportunities and Stages. Let's say our process has 5 stages:
Stage 1- Suspect
Stage 2 - Qualify
Stage 3 - Proof of Concept
Stage 4 - Negotiate
Stage 5 - Close Won
The desired outcome is to see how much revenue we had available at the top of the funnel Stage 1; how much made it to Stage 2; etc. until at the bottom we see the final amount that made it to the last stage 'Close Won'.
To start; pick the Funnel Chart Type from the drop-down list.
Leave Categories blank.
Add Total RevenueSUM(Revenue) as the first Value field.
Now; let's add filtering fields to the formula. Syntax will be (SUM([Revenue]);[Stage])
Inside the formula click on [Stage] and choose Filter.
Pick all stages that belong to this process. In this example; the five below highlighted in blue. Click OK.
Now duplicate the Value field four times (right Click followed by 'Duplicate') and name your stages.
The end result looks promising; but the data is correct only for the first stage right now.
Click on the pencil icon to modify the formula for Stage 2. Qualify. In the formula; click on Stage. In the opened filter; click on Suspect on the bottom to gray it out. This is how you remove the deals that are still in the previous stage 'Suspect' and have not made it to Stage 2 yet.
Follow the logic above and go to the next Value; Stage 3. POC and gray out deals that are still in Stages 1 and 2; 'Suspect' and 'Qualify'.
Continue until the last stage. If desired; adjust formatting and apply time frame filters to view progress during a certain period of time such as this quarter; last month; etc.
