4 Replies Latest reply on Aug 29, 2018 5:10 PM by Jeffrey Taylor

    Need help with a determining how much of a category a subcategory represents when the subcategory spans multiple categories

    Jeffrey Taylor

      I am working on a project involving US Census data and "Metropolitan Statistical Areas" or MSAs.

      MSAs are metro areas that typically contain more than one county. In some cases, an MSA can cross state lines, and this is where my trouble is.

      I am trying to find out how much of a state resides within an MSA. My data set is a single table with state, county and county population. Where applicable, it also includes the MSA code and MSA name.

       

      Getting population totals for states and MSAs on their own is easy enough using LOD calculations, and these work fine. The trouble is that this doesn't work properly when the MSA spans multiple states. I can break it out and view it as a table by summing county populations, but this is limiting. My goal is to visualize the MSAs on a map (I have the Census spatial files integrated for this purpose) and then be able to color the MSAs by how much a state's population they represent. I know that getting multiple spatial files involved is a different challenge, so I am just looking for help on the calculation side of things - one thing at a time!

       

      Here is an example of the problem I am having:

      MSA NameStateState PopulationMSA PopulationMSA Population % of stateCounty PopulationCounty Population % of state
      Chicago-Naperville-ElginIllinois12,830,6329,461,10573.74%8,586,60966.92%
      Indiana6,483,8029,461,105145.92%708,07010.92%
      Wisconsin5,686,9869,461,105166.36%166,4262.93%

      I hope this helps illustrate the problem I am having (and this is also on "Sheet 2" in the attachment). In Illinois, the Chicago MSA represented about 67% of the state's population, but the LOD calculations against both the state and MSA show it to be about 74%. The same problems causes values of >100% for the other two states.

       

      I assume there is a way to do this through an LOD calculation, but I can't figure it out. I can get state population by summing county populations when a county resides in a state. I can get MSA population by summing county populations when a county resides in an MSA. I can't seem to add the state dimension to the MSA calculation. If I can get this problem solved, then I get learn how much of a state the MSA represents, which is the dominant state in the MSA in terms of relative population, etc.

       

      I use Desktop 10.2 (required for our Server rev), and a packaged workbook is attached.

       

      I appreciate any help you can provide!