4 Replies Latest reply on Apr 25, 2017 5:14 AM by Tony Durst

Trouble with Joined Data in Calculation

Hi all,

I'm new to Tableau, so I hope someone has encountered this before. I'm working with some crime data from Chicago's data portal. I have record level crime report data that I have classified into Arrests, No Arrests and Combined. I brought in population for all of Chicago for the ten years that I'm working with. I joined the data on Year. I want to calculate total arrests divided by population (per 100k). To get a crime rate stat. So for example, 25,000 burglaries / (2.7M people/100k) is a burglary rate of 925.9 per 100k residents.

The problem is that when I bring in population per year I need to choose a measure. I choose avg wanting an average per year which should be the population for that year as ever year as a constant population number. What I get though is an average across all years no per year. I've tried using LOD to solve but can't crack the code. Any helpful ideas would be much appreciated!

• 1. Re: Trouble with Joined Data in Calculation

Hi Tony, glad to see you are working on Chicago crime!  I'm from Chicago originally but love the weather a whole lot better down here in the ATL.  Can you post your workbook so we can understand the problem better?

• 2. Re: Trouble with Joined Data in Calculation

Here is the attachment, thanks!

• 3. Re: Trouble with Joined Data in Calculation

Thanks.  Have you checked if your Chicago population data source is correct?  There is actually a variation by year at very small amount.

1 of 1 people found this helpful
• 4. Re: Trouble with Joined Data in Calculation

I was expecting to see a difference but I couldn't post my actual file because it has over 3M records. So I created a skinny version and posted it. I noticed that it was working like I expected with slight variations in years from the little look up file I created from census data. I went back and removed all of my data sources and brought them back and rejoined them. Low and behold it calculates correctly now.

So I guess I learned:

1.  Try recreating the problem with a smaller data set to see if it persists

2.  This forum is moderated well and other people want to help

Have a great day!