11 Replies Latest reply on Oct 13, 2017 10:11 AM by Prasad Moyarath

    Sales Funnel Chart

    Aaron Evans

      I want to create a Funnel Chart and have had some success, where it fails is when the size of the measures distort the funnel shape because they are ever changing numbers. How do i standardize the Funnel so that the process steps remain the same order and it does not matter what numbers the measures contain???

       

      Any ideas??

        • 1. Re: Sales Funnel Chart
          Thomas McCullough

          Aaron,

           

          If you never want the funnel shape to change, consider placing a static image on a dashboard of a funnel.  Then float multiple sheets containing the numbers you need on top of the funnel image.  Alternatively, check out the sort on the dimension that is partitioning the funnel.  You can manually sort the dimension so it never changes order, despite changes in numbers.

           

          Cheers,

           

          Tom

          • 2. Re: Sales Funnel Chart
            Rody Zakovich

            Aaron,

             

            You could do something with a calculated field used for the Measure Sizes, Something like

             

            IF MIN([Category]) = 'Top' THEN MIN(9)

            ELSEIF MIN([Category]) = 'Middle' THEN MIN(5)

            ELSEIF MIN([Category]) = 'Bottom' THEN MIN(1)

            END

             

            This way, the Size of the Bars are always consistent. Then you can just add Measure Values as Text Mark labels.

             

            Depends on how your Viz is laid out though. Can you provide a Screenshot, or an example workbook?

             

            Regards,

            Rody

            • 3. Re: Sales Funnel Chart
              Aaron Evans

              Thanks for your input guy's..............

               

              Both good suggestions, but i think in essence i would like to see the Height of each bar/area change relative to the measure value as to represent change in the movement of the sales pipeline, without the reordering phases/stages of process or distorting funnel shape??

               

              Any further ideas?

               

              Aaron

              • 4. Re: Sales Funnel Chart
                Rody Zakovich

                Hello Aaron,

                 

                Not sure I quite understand what you are after?

                 

                You don't want the Length to change, you just want the Height?

                 

                Can you provide a screenshot of what you are seeing vs what you would like to see (The latter being just some sort of mock up).

                 

                Regards,

                Rody

                • 5. Re: Sales Funnel Chart
                  Aaron Evans

                  Hi Rody,

                   

                  Here is the funnel as i would like to see it in Tableau in Excel.

                   

                  As you can see, the shape of the funnel never changes, just the size of each bar relative to the value.

                   

                  In tableau, when you create the "Funnel", the bars seem to distort in width depending on the value?

                   

                  Ignore it's current format in tableau, i haven't yet change the bars to areas, but the issue is the same.

                   

                   

                   

                  Hope i've made some sense??

                   

                  Aaron

                  • 6. Re: Sales Funnel Chart
                    Rody Zakovich

                    Hey Aaron,

                     

                    From what I can tell, the aim is to show how distribution is changing through time from multiple segments (While keeping a 100% scope in mind).

                     

                    Rather than using multiple funnels, it may be better to use a Multi-Series line graph, or multiple 100% Stacked Bars.

                     

                    Great article/discussion on the benefits/downsides of these two chart types in various situations Visual Business Intelligence – When Are 100% Stacked Bar Graphs Useful?

                     

                    Using funnels would make it very hard to see variations in size(% of Total contribution)  from one point in time to another. Calling it a "funnel" may also confuse people, since a Funnel implies a Top-to-Bottom relationship, where the Top is the MAX Number, and Bottom is the MIN number.

                     

                    Also, there is no easy way to create "Clean" funnels in Tableau. So though we could get to something that looks like what you are trying to do, it would be "hacky" and wouldn't have a clean look.

                     

                    Apologies if this wasn't what you were looking for, but I wanted to throw in a couple of different ideas,

                     

                    Best regards,

                    Rody

                    • 7. Re: Sales Funnel Chart
                      Rajeev Pandey

                      Dear Evans,

                      I am just trying to help , i dont how far it will help you but please give a tryatleast. Hope this helps.

                       

                       

                      My data structure and sample data.

                       

                       

                            

                      Funnel Chart with single color to all the dimensions.

                       

                      Steps 1

                      Create one calculated field, name it as -Count

                      formula to add in calculated field is -[Count]

                       

                       

                       

                      Step 2

                      Bring status (dimension) to rows shelf, bring count (measure) to column shelf.

                      Drag newly created calculated field and drop it into column shelf, left to already present count(measure).

                       

                      Step 3

                      Now you can see bar chart with two part one for positive values and one for negative values.

                       

                      Step 4

                      Sort it descending .

                      Step 5

                      Change the graph type from BAR to AREA.

                       

                      Step 6.

                      Funnel chart is ready. Now add status onto Label marks cards.

                                                

                      Funnel chart with different color for each dimension.

                       

                      In my sample data, we have following dimension values.

                      1) Registered.

                      2) Download.

                      3) Hits.

                      4) Attended.

                       

                      Step 1

                      So we have to create 2 calculated field for each dimension, one for positive and one for negative.

                       

                      Following is the calculated field I have created.

                      1) Registered

                      calculation :-

                      if ATTR([Status])='Registered' or LOOKUP(ATTR([Status]),-1)='Registered' then SUM([Count]) END

                       

                      2) -Registered

                      Calculation :-

                      if ATTR([Status])='Registered' or LOOKUP(ATTR([Status]),-1)='Registered' then -SUM([Count]) END

                       

                      3) Hits

                      Calculation :-

                      if ATTR([Status])='Hits' or LOOKUP(ATTR([Status]),-1)='Hits' then SUM([Count]) END

                       

                      4) -Hits

                      Calculation:-

                      if ATTR([Status])='Hits' or LOOKUP(ATTR([Status]),-1)='Hits' then -SUM([Count]) END

                       

                      5) Attended

                      Calculation :-

                      if ATTR([Status])='Attended' or LOOKUP(ATTR([Status]),-1)='Attended' then SUM([Count]) END

                       

                      6) -Attended

                      Calculation:-

                      if ATTR([Status])='Attended' or LOOKUP(ATTR([Status]),-1)='Attended' then -SUM([Count]) END

                       

                      7) Download

                      calculation:-

                      if ATTR([Status])='Download' or LOOKUP(ATTR([Status]),-1)='Download' then SUM([Count]) END

                       

                      8) -Download

                      Calculation :-

                      if ATTR([Status])='Download' or LOOKUP(ATTR([Status]),-1)='Download' then -SUM([Count]) END

                       

                      We are creating these calculated field in order to sum it up by each dimension instead doing it as combine sum.

                      And negative calculated field are created in order to plot graph to negative side (similar -count in previous example).

                       

                       

                       

                      Example screenshot.

                       

                      Step 2

                      Bring status (dimension) to rows shelf.

                      Bring measure values (measure) column shelf.

                       

                      This is what you will see after step 2.

                       

                      Step 3.

                       

                      Now remove unwanted measure from measure values.

                      in this case we have

                      1) Sum(count)

                      2) sum(-count)

                      3)Sum(Number of records).

                                                 

                      Step 4

                      Now select all the remaining measure and change its Compute using to Table(Down)

                       

                      Step 5

                      Sort the graph descending.

                        

                      Step 6

                      Now change the graph type from BAR TO AREA

                      This is how it will look.

                       

                      Step 7

                      Now , Go to analysis tab (top of screen), click on it , change stack marks from on to off.

                       

                          Graph will look like this after change.

                       

                      Step 8

                      Drop status from dimension on color marks card.

                       

                      Its READY.

                      Funnel Chart with different colored dimension.

                      • 8. Re: Sales Funnel Chart
                        Aaron Evans

                        Thanks for your reply Pandey,

                         

                        I have already achieved this objective, the problem with this solution is when the values in each of the phases are larger than the phase above it distorts the funnel shape, this is why i am looking for a more robust solution.

                         

                        Aaron

                        • 9. Re: Sales Funnel Chart
                          Aaron Evans

                          Hi Rody,

                           

                          I have been using stacked Bar graphs previously and agree they can be effective, there is a call within my business, to show our sales process and the opportunities contained within it in a traditional "Sales Funnel" hence my quest for a solution.

                           

                          The nearest i have come to finding a solution is contained within this thread
                          Funnel chart

                           

                           

                          But i can't get it to work with my data, the example is based on data in a excel spreadsheet, but i take my feed from our sales tool directly into Tableau.

                          • 10. Re: Sales Funnel Chart
                            Oindrila B

                            Rajeev, it's really a good step by step guide!

                            • 11. Re: Sales Funnel Chart
                              Prasad Moyarath

                              When I put labels, it repeats as shown belowFunnel.png

                              Any solution to make the label appear only once?