8 Replies Latest reply on Jul 9, 2014 10:23 AM by Joshua Milligan

    using filter to benchmark a company in its industry

    Saghar Tamaddon

      I have a table including annual sale per sales person. It also has a column for the company and the industry they belong to. Something like this:

       

      Sales RepCompanyIndustry
      Sales Amount
      TomC1Software1000
      JohnC1Software1200
      MaryC2Internet2500
      BobC3Software3000

       

      I want to build a simple dashboard that shows each Company's average sales amount by Sales Rep and compares it to the average sales amount by Sales Rep in the company's industry - basically benchmarking avg sales by a rep within their industry.

       

      Example:

      C1's avg sales per Sales Rep: 1100

      Software industry's avg sales per Sales Rep: 1333.33

      Result: C1's Sales Rep are behind industry average

       

      How would you do this Tableau? And how would you visualize it?

       

      Many thanks!

       

      Joe Mako

      Patrick Van Der Hyde

      Joshua Milligan

        • 1. Re: using filter to benchmark a company in its industry
          Joshua Milligan

          Saghar,

           

          There are multiple ways to do this.  The first thought that comes to me is to create a calculated field like this:

           

          [Avg Per Sales Rep]

          SUM([Sales Amount]) / COUNTD([Sales Rep])

           

          Then you can use a combination of techniques to compare different levels of detail (e.g. Company / Industry).  The "best" way to visualize it will depend on your data, your audience, what you want to communicate, etc...

           

          If you want to compare multiple levels of detail, I'm aware of several options.

          In the same view:

          1. Table Calcs

          2. Data Blending

          3. Sets

           

          Other ways:

          4. Multiple views on a dashboard

           

          If you have a larger sample of data and can post a packaged workbook with a start on what you are trying to accomplish, I'd be happy to take a look!

           

          Regards,

          Joshua

          1 of 1 people found this helpful
          • 2. Re: using filter to benchmark a company in its industry
            Joe Lipski

            Hi Joshua,

             

            Would you be able to explain this is a bit more detail please.

             

            I am looking to do a very similar thing and cannot seem to find the best way to achieve it.

             

            Using the 'Sample - Superstore Subset Data', I'm essentially looking to have a dashboard/worksheet with four bars on it:

             

            1. AVG sales for City of choice (e.g. Aberdeen)

            2. AVG sales for state of city selected (e.g. South Dakota)

            3. AVG sales for region of city selected (e.g. Central)

            4. AVG Sales for whole dataset

             

            Essentially a benchmarking dashboard/chart where whatever city you choose the other bars update.

             

            I've tried a few different things, but can't seem to crack it.

             

            Any advise would be greatly appreciated.

             

            Thanks,

            Joe

            • 3. Re: using filter to benchmark a company in its industry
              Joshua Milligan

              Joe,

               

              There are a couple of ways this might work.  Will the filter need to operate on more than one worksheet?  Do you want to drive the action from a worksheet, quick-filter, or parameter? 

               

              Would you be able to post a sample workbook so I can see which direction you are taking?

               

              Regards,

              Joshua

              • 4. Re: using filter to benchmark a company in its industry
                Joe Lipski

                Hi Joshua, thanks for the response.

                 

                The store (or city) filter will need to apply to either each bar or each worksheet - whichever is the best way to go about this.

                 

                I've attached a packaged workbook with a basic example what I'm trying to achieve - Although I'm pretty sure I haven't done this in the best way

                 

                Essentially on Dashboard 1 I want to be able in the drop down to be able to pick a city, this will the update that cities bar (in this case Aberdeen), All cities will always stay the same. State will update with whichever state the chosen city is in and the same with region.

                 

                So if you pick Aberdeen it will be: Aberdeen, All, South Dakota, Central

                if you pick Abilene it will be: Abilene, All, Texas, Central

                if you pick Abington it will be: Abington, Massachusetts, East

                etc.

                 

                I hope this helps.

                 

                Thanks again,

                Joe

                • 5. Re: using filter to benchmark a company in its industry
                  Joe Lipski

                  Hi Joshua,

                   

                  Have you had any luck? Did I clarify what I meant?

                   

                  Thanks,

                  Joe

                  • 6. Re: using filter to benchmark a company in its industry
                    Joshua Milligan

                    Joe,

                     

                    Thanks for the reminder to come back to this.

                     

                    Here's an option for using Data Blending that allows it to all be done in a single view with a single filter.  Of course you could break the view into multiple views if you'd like.

                     

                    I've attached a workbook to demonstrate.  Basically, I duplicated the data source for each level of detail I wanted.  Then I activated the blend (click the link buttons on the fields to make them active) but only on the fields that define the level of detail I want (e.g. State, Region, or even no linking field to allow it to blend to everything).

                     

                    Regards,

                    Joshua

                     

                    1.png

                    • 7. Re: using filter to benchmark a company in its industry
                      Joe Lipski

                      Josh, apologies for coming back to you!

                       

                      This is exactly what I was after and I just couldn't quite get to it! I've repeated what you have done on my data and works brilliantly. Thank you very much for the help.

                       

                      Joe

                      • 8. Re: using filter to benchmark a company in its industry
                        Joshua Milligan

                        Joe,

                        Glad to help!  And glad it worked!

                        -Joshua