3 Replies Latest reply on Mar 8, 2019 10:50 AM by Ken Flerlage

    Comparing per capita to national average

    Garrett Dube

      Hi there,

       

      I'm trying to compare per capita to the national average and then show that a certain state is 20% above national average. This would ideally be on the same sheet as the per capita rankings, but that is not necessary. The per capita sheet is a list of all the states showing their per capita and the column total at the bottom is the average across all the states. Illinois should be 48.25% below national average doing it on a calculator. Any help would be appreciated. Attached is my workbook.

       

        • 1. Re: Comparing per capita to national average
          Ken Flerlage

          To begin with, I believe you Per Capita calculation is wrong. You have multiple sub-categories, which result in multiple records, but your 2019 population data is only a single record. When you join these together, that number is being duplicated multiple times (one for each sub-category), so your sum of the population gets duplicated as well. I think the following should correct that:

           

          SUM([Total Volume])/AVG([2019 Population])

           

          Next, we can create a new calculated field to get the overall per-capita:

           

          Per Capita All

          // Get the per capital for all states.

          SUM({FIXED : SUM({FIXED [State]: SUM([Total Volume])})})/

          SUM({FIXED : SUM({FIXED [State]: AVG([2019 Population])})})

           

          Note: This uses FIXED LOD calculations. If you're not familiar with those, I'd suggest reading the following: FIXED Level of Detail Expressions - Tableau

           

          Also, you should be aware of how LODs can affect various things, particularly dimension filters so I'd suggest reading the following: Tableau's Order of Operations - Tableau

           

          Now, to get the variance from the national average, we can create one more calculated field:

           

          Variance from National

          // Variance of the state's per-capita to the national per-capita

          [Per capita]-[Per Capita All]

           

          Finally, you can use that to visualize the difference.

          See attached.

          • 2. Re: Comparing per capita to national average
            Garrett Dube

            Thank you Ken for the help. I'm glad you caught the per capita, never thought about the duplicated in regard to the per capita calculation. I'm still learning LODs and figured I needed to use one here but wasn't sure where to start. Thanks for posting links, much appreciated.