2 Replies Latest reply on Mar 31, 2015 5:33 PM by Mark Labovitz

    Creating a per capita normalization

    Mark Labovitz

      I have a list of names containing some duplicates.  Each name also has a state associated.  I want to create a value which has the count of unique names per state and use this value as a denominator for other values aggregated by state.  I used the countd() function and can display the distinct counts by state, but when I go to use this in another computation I get an error for using aggregated and unaggregated data in the same computation. I have seen a suggest involving use of  the index() function, but could not decode the details of what was being suggested.  Thanks for the help.


      Mark Labovitz

        • 1. Re: Creating a per capita normalization
          Steve Mayer

          You should be able to achieve what you are after with straight calculated fields, although a specific example posted in a packaged workbook would help a lot.


          Assuming your calculated field for unique names per state (let's call it NamesPerState) looks something like this (which will work for single states as well as aggregating across states):


          COUNTD([Name]) / COUNTD([State])


          and you have another measure like Revenue, you would calculate Revenue per unique name like this:


          SUM([Revenue]) / [NamesPerState]


          Note that any calculated field that uses NamesPerState has to use aggregate functions (like SUM, AVG, etc), because you cannot mix aggregate and non-aggregate values in a single calculated field.


          Hope this helps. If you post a packaged workbook with sample data & a specific use case, we'll better be able to serve in the future.



          • 2. Re: Creating a per capita normalization
            Mark Labovitz

            Steve --


            Thanks for your assistance.  Much appreciated.


            Mark Labovitz