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.

           

          -Steve

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

            Steve --

             

            Thanks for your assistance.  Much appreciated.

             

            Mark Labovitz