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

# Summing data that meets certain criteria within a CASE statement

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!

• ###### 1. Re: Summing data that meets certain criteria within a CASE statement

Any chance you could share a sample of your data? There are so many different things that come into play here, including the structure of your data, the level-of-detail, etc. that it's difficult to properly answer it without seeing it.

• ###### 2. Re: Summing data that meets certain criteria within a CASE statement

CASE [Region]

WHEN "North East" THEN

([Car Count]/2644727)*1000

END

• ###### 3. Re: Summing data that meets certain criteria within a CASE statement

Thanks for your response but I get the same results even with the brackets.

• ###### 4. Re: Summing data that meets certain criteria within a CASE statement

Sure - I've uploaded the Excel data I am pulling from, with the first tab being the car numbers and the second tab being the mapping info.

Thanks!

• ###### 5. Re: Summing data that meets certain criteria within a CASE statement

I would do a FIXEd or IF Statement, so

There are multiple ways to get what you are trying, but using CASE statement, Here it is:

Thanks

Deepak

• ###### 6. Re: Summing data that meets certain criteria within a CASE statement

How do you link the two tabs together? There is no common field for a join or a blend.

• ###### 7. Re: Summing data that meets certain criteria within a CASE statement

Maybe create a Calc to get the count of cars for "North East' using a LOD

[CNT in NE]

{ SUM(if [Region]='North East' then [Car Count] else 0 END)}

Then use this Calc in further calculations

Case [Region]

When 'North East' Then [CNT in NE]/2644727)*1000

....

....

END

• ###### 8. Re: Summing data that meets certain criteria within a CASE statement

I've written a calculated field for the region so that the region code produces a town name and you can join on that.

• ###### 9. Re: Summing data that meets certain criteria within a CASE statement

Thank you - I will give this a go.

• ###### 10. Re: Summing data that meets certain criteria within a CASE statement

Ok thank you - I will give that a try.