3 Replies Latest reply on Apr 12, 2016 7:14 AM by Stephen Lavery

# Compare value to city average and national average

I need to compare the number of pages printed by a particular user against the average pages printed by the people on that city and in the whole country.

Some people don't have City data so on that case I need to compare against the State average and then the Country average.

I don't need to show the whole table with averages, basically when a user select his name I just need to show if he's above/below his City/State average and above/below the Country average.

I'm attaching a sample with mock data.

Thanks!

• ###### 1. Re: Compare value to city average and national average

Hi Juan,

I don't have your version or tableau but if you decide to upgrade to 9.2 I've attached it. I believe I have the results you're looking for:

How I did this was first create this calculated field called 'Average State':

{EXCLUDE [Name], [Level 3]: SUM([Pages Used])}/{EXCLUDE [Name], [Level 3]: COUNTD([Name])}

Then this calculation which is 'Average City':

{EXCLUDE [Name], [Level 2]: SUM([Pages Used])}/{EXCLUDE [Name], [Level 2]: COUNTD([Name])}

Then this calculation which chooses the average state calculation if the city is null. I called this one 'Average (City/State)':

IF ISNULL([Level 3]) THEN [State Average] ELSE [City Average] END

Then I created a 'Difference from Average' calculation:

AVG([Average (City/State)])-SUM([Pages Used])

And finally a calculation which says whether this is up or down on the average of the city (or state if the city is null):

IF [Difference from Average]=0 THEN "Average Pages Used"

ELSEIF [Difference from Average]>0 THEN "More Pages Used"

ELSEIF [Difference from Average]<0 THEN "Less Pages Used"

END

A long winded method but it works! Let me know if this helps.

Regards,

Stephen

• ###### 2. Re: Compare value to city average and national average

This works Stephen! Thanks a bunch!