1 Reply Latest reply on Mar 9, 2016 10:53 AM by Ryan Eavey

    calculating variance between 2 averages

    George North

      OK so I need a little help doing something in Tableau that I do in Excel all the time.. and i dont know why this is so hard for me here

       

      Below is a Bit of the data i am working with ... It has sales numbers for hotel rooms  from a Project ( the Hotel ) and the account ( a company like IBM)

       

      what I am doing is simply taking the Average Daily Revenue (ADR) for an account (Revenue / Days)  then I subtract that from the ADR of the Project (the total of all the days and Revenue for all accounts) so I can see what accounts are bringing the Total ADR UP and what are bringing it down..

            

      Project #Account #DaysRevenueADRADR Var from Project
      055300407531$2,956.94$95.39(11.64)
      055300842831$3,214.40$103.69(3.33)
      055302827911$1,046.91$95.17(11.85)
      055304669010$690.03$69.00(38.02)
      055305992531$3,348.00$108.000.98
      055306678062$6,138.00$99.00(8.02)
      055307165831$3,069.00$99.00(8.02)
      05530849009$727.20$80.80(26.22)
      055308930622$2,724.00$123.8216.79
      055309866028$3,056.33$109.152.13
      055311986729$4,234.00$146.0038.98
      055313374231$3,684.79$118.8611.84
      Total Project326$34,889.60$107.02

       

       

      My problem is that in Tableau I have filters going so that when I select a filter for a particular account all the other accounts get removed from the Total Projects numbers and my variance result always ends up at 0 once I apply the filter..

       

      How do I lock the Total project ADR so it is not impacted by the filtering

       

      now to make things a little more complex, in the end I have 150+ Projects and many of those Projects share accounts.. so I need to be able to Filter on a particular project so I am only seeing the results for accounts that are staying at a specific Project.