3 Replies Latest reply on Oct 27, 2017 1:57 AM by Simon Runc

    Exclude the selection from one sheet to another sheet

    lalitha p

      Hello Everyone,


      I have a requirement something like this. I have a report at employee level, where i am showing some information regarding the selected employee , say Gender, his/her Dept along with few metrics. I am going good with this. But I want to show comparison here like this.. i.e say i have selected myself and i was displaying say Rev, Overall Cost, COR etc and i want to have comparison with all other employees like this.


      Say My Cost YTD is : 20k$ and say rest all employees is 500k$ and i want to have a comparison with 20k$ to the rest figure 500k$. I am good to display in different sheet and bar on bar in the same sheet. 

      I have a filter employee name, where my whole worksheets is getting filtered out by the selected employee, my idea is something like i have to exclude that selected employee and get the info for rest of the ppl. But really didnt get any idea how i can acheive this. I was trying with actions to the other sheet but couldn't . Could anyone please help me on this.



      Lalitha K Prabhala

        • 1. Re: Exclude the selection from one sheet to another sheet
          Simon Runc

          hi lalitha,


          So one way that might work for you is to use FIXED LoDs. These are computed before any "regular" dimension filters are applied, so you can filter to an individual, but still return the AVG/SUM...etc for all.


          In the attached you can select a user, and the [Average Spend of All Customer] field will still return the AVG spend of all. The calculation is





          {FIXED [Customer Name]: SUM([Sales])}




          Hope that helps

          • 2. Re: Exclude the selection from one sheet to another sheet
            lalitha p

            Hi Simon,


            Thanks for the answer. But i want to exclude that particular user and then i have to get avg for the remaining. I mean i have to exclude my selection to few sheets (where i want avg for remaining ppl) and only that selection for few sheets (where i want to see that particular selection information) . this is all to compare him/her to other resources in the organization.


            My idea is actually something like this ,

            Say i have my employee data like this. Employee Name and Cost. Now i have filter employee name, where i have selected "David" then i will get few information about David along with few metrics say, his projects worked so far, gender, experience, skill set and cost, revenue generated, COR etc. So now i want to have David vs Total(Rem ppl ) Main point here is : If David level is Senior Consultant,  ideally he has to get comparison with the senior consultants group only.

            Either i want to show it Pie or Bar over Bar if not at least to get comparison in another sheet. But i am getting any idea to do this.



            Imagine If data is like this, Then David has to compare against Lethu and Kalpa only as they belong to same Grade. And more like i want to show as Bar on Bar Format. This is how exactly i am trying to show in my dashboard.




            Lalitha K Prabhala

            • 3. Re: Exclude the selection from one sheet to another sheet
              Simon Runc

              OK I think I see what you want (although thankfully Tableau wont recreate your 3D bar chart!!)


              So one way that might work is this....


              I've created the following calc


              [Cost Excluding Selection: Everyone]

              SUM({FIXED: SUM([Cost])}) - SUM([Cost])


              So the FIXED LoD get's the SUM of Sales for everyone, and then takes off the selected user(s) (such as David)



              and then we can use measure names/values to get this



              You also mentioned about having this, but only with the selected users payband...we can change the formula to this

              [Cost Excluding Selection: Within Same PayGrade]

              SUM({FIXED [Grade]: SUM([Cost])}) - SUM([Cost])


              Now this, obviously, doesn't work where there is only one person in a grade, but hopefully you follow the logic and can adapt it to your final use