12 Replies Latest reply on Dec 4, 2013 7:53 PM by Son Stegmaier

    Tableau Vlookup Equivalent

    James Bostock

      All,

       

      I'm looking to compare industry averages against the industry of a list of accounts. I've the data setup as below:

       

      Account Name | Account Industry | Account Revenue | Avg Industry Revenue

       

      There are several accounts in the list, each with their own industry. I'd like to be able to see how they stack up against a list of all accounts with the same industry.

       

      Is there a way to do this in Tableau?

       

      Thanks

       

      James

        • 1. Re: Tableau Vlookup Equivalent
          Manas Ranjan Kar

          You might not need VlookUp here. Drag  Ac Industry and Ac Name to the rows/colums pane, in that order. Then Drag Ac revenue to the Labels. You should get a view which tells you the accounts by industry and their respective sum of revenue.

          • 2. Re: Tableau Vlookup Equivalent
            Matt Lutton

            I believe Manas is correct, this is very simple to do in Tableau!

            • 3. Re: Tableau Vlookup Equivalent
              James Bostock

              I should probably specify that I don't actually have the calculated field  Avg Industry Revenue. (major point, I know). What I do have is the other 3 fields and I'm hoping the calculated field would do the vlookup.

               

              Thanks

               

              James

              • 4. Re: Tableau Vlookup Equivalent
                Matt Lutton

                Right.  Just do as instructed, only use the AVG aggregation instead of SUM.  So:

                 

                Drag  Account Industry and Account Name to the rows pane, in that order. Then right click+drag the Account Revenue pill to the "Text" shelf (or you can place it on the Rows shelf as a discrete (blue pill) and choose the AVG aggregation). You should get a view which tells you the accounts by industry and their respective average of revenue.


                Here is an example using Sample Superstore--same setup, only with Customer Segment > Customer Name on Rows shelf, and AVG(Sales) on the text shelf, so the final view displays customers by segment with their average sales in each segment.

                Avg sales by Customer in Segment.png

                1 of 1 people found this helpful
                • 5. Re: Tableau Vlookup Equivalent
                  James Bostock

                  Using the above SuperStore example I'm looking to have the below:

                   

                  Customer Name | Customer Segment | Total Sales for Customer | Avg Sales for the Customers Segment

                   

                  I'd like to compare the Customer Revenue to the Avg Customer Segment to understand how the customer differs from the average in the customer segment.

                   

                  I think the above is looking at the customer Avg by segment, rather the customer vs the avg in the segment

                   

                  Thanks

                   

                  James

                  • 6. Re: Tableau Vlookup Equivalent
                    Matt Lutton

                    This is why we keep asking for a workbook and a clear explanation of what you are wanting... no offense, intended, but we are only now getting to what you are expecting as an outcome.  Knowing what you want, and describing it clearly will always result in the best help you can get on the Forums.

                    • 7. Re: Tableau Vlookup Equivalent
                      James Bostock

                      Apologies for being unclear. Let me use the demo data and attach a workbook.

                      • 8. Re: Re: Tableau Vlookup Equivalent
                        James Bostock

                        I've attached the workbook too, but what I'm trying to do is have the calculated field "Sales by Segment" SUM/AVG the segments sales for each customer, rather than the customers average in each segment.  

                         

                        In excel I'd either do a vlookup or SUMIF depending on how the data is available. Sales by Segment would take a look at Aaron Day, see that he was in the Corporate Segment and give me the average of all the customers in that segment. It would then do the same for each of the customers.

                         

                        Segment.PNG.png

                         

                        Thanks

                         

                        James

                        • 9. Re: Tableau Vlookup Equivalent
                          Matt Lutton

                          When you say "total sales by customer", do you mean per segment, or their overall total?  I'm still not clear on what you want.

                          • 10. Re: Tableau Vlookup Equivalent
                            James Bostock

                            Assuming no filter on date then the Sales by Customer would be a simple SUM(Sales) and would represent the overall total by that customer. Each customer will have a single line and only 1 segment.

                             

                            Sales by Segment would be the total (or avg depending on the aggregation) of sales done in that segment by all customers.

                            • 11. Re: Re: Tableau Vlookup Equivalent
                              Matt Lutton

                              Gaining an understand of how Tableau creates a view will help, and as you work with the software, you will begin to see how Tableau creates a view.  Everything is dependent on the layout of pills--mainly the dimensions.    This is important because simply dragging SUM(Sales) into the view will not give you the total sum of sales for each customer, but rather, the sum for each customer, by segment.  Some customers have more than one segment in this sample data, so we'd have to use a Table Calculation to get the total sum of sales by customer with customer segment in the view.

                               

                              If it were me, I'd simply create two sheets.  One showing the Customer segments with their respect Avg or Total sales, then a second sheet for the Customers and their total sales.  Then, you can use an action to filter for each segment, effectively comparing the Avg Sales by segment to the Customers total sales who are in that segment... hopefully, that makes sense. I have set that up in the attached--note that the Result Sheet actually does have the Customer Segment on the level of "Detail" shelf, which means a Table Calc was used to compute the total sales by customer across all segments--it also has to be in the sheet in order for the action filter to work.

                               

                              I believe you could get this all in one sheet, but I had trouble finding a way to display the average by customer segment in the same sheet.  Some other forum member with more Table Calc experience can probably come up with a better solution.

                               

                              I do hope this helps.

                              1 of 1 people found this helpful
                              • 12. Re: Tableau Vlookup Equivalent
                                Son Stegmaier

                                I added a couple window function to get it all on one sheet.  Take a look and see if this helps.

                                windowfunction.PNG.png