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

# calculating variance between 2 averages

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 # Days Revenue ADR ADR Var from Project 0553 004075 31 \$2,956.94 \$95.39 (11.64) 0553 008428 31 \$3,214.40 \$103.69 (3.33) 0553 028279 11 \$1,046.91 \$95.17 (11.85) 0553 046690 10 \$690.03 \$69.00 (38.02) 0553 059925 31 \$3,348.00 \$108.00 0.98 0553 066780 62 \$6,138.00 \$99.00 (8.02) 0553 071658 31 \$3,069.00 \$99.00 (8.02) 0553 084900 9 \$727.20 \$80.80 (26.22) 0553 089306 22 \$2,724.00 \$123.82 16.79 0553 098660 28 \$3,056.33 \$109.15 2.13 0553 119867 29 \$4,234.00 \$146.00 38.98 0553 133742 31 \$3,684.79 \$118.86 11.84 Total Project 326 \$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.

• ###### 1. Re: calculating variance between 2 averages

Hi, George,

I think you can accomplish this by using a Level of Detail Calculation, below.

{FIXED [Project#]: SUM(Revenue)/SUM(Days)}

This will give the Total ADR for each project which can then be used in other calculations to find the variance.

Hope this helps!

Thanks,

Ryan