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

    Compare value to city average and national average

    juan.tobon

      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
          Stephen Lavery

          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
            juan.tobon

            This works Stephen! Thanks a bunch!

            • 3. Re: Compare value to city average and national average
              Stephen Lavery

              No problem. Glad it worked!