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




      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!