10 Replies Latest reply on Jan 8, 2019 9:22 AM by Chris Jones

    Summing data that meets certain criteria within a CASE statement

    Chris Jones

      I am trying to create a calculated field but it is not quite working how I want at the minute.

       

      Currently, my standard data is showing the number of something (call it number of cars) per region (I have mapped 10 regions).

       

      However, I want to normalise this to account for population differences between regions. I therefore want my measure to be number of cars per 1,000 people.

       

      I have currently written:

       

      CASE [Region]

      WHEN "North East" THEN

      [Car Count]/2644727*1000

      etc.

      END

       

      With the number 2644727 being the population figure I am using. However, I think this is currently dividing the total Car Count, rather than only the car count in the North East.

       

      Can you please advise how I can get the formula to only sum the number of cars, where the region is (in this case) "North East"?

       

      Many thanks!