Power Pivot Dax Formula to calculate Population Per Capita Value

Joe Fowler

New Member
Joined
Dec 3, 2011
Messages
2
I am trying to calculate the Per Capita utilization of the 311 Non-Emergency Phone System in my home city for each of the 280 defined Neighborhoods in my home city.

I have two Power Pivot tables in my data model:


  1. The first table consists of 311 Telephone Calls (similar to 911 calls, but non-emergency, e.g. a parking violation or a request for trash pickup) for my home city. There is one row for each 311 Call made over the last 6 years - around 4,000,000 rows in total. Among the various columns there is the Neighborhood_Name that the 311 call came in from.
  2. The second table consists of demographic data for each of the 280 Neighborhoods in my home city, 1 row for each neighborhood. There is a many-to-one relationship between the 311 Call Table and the Neighborhood Demographic Table. There is a Population column in the Neighborhood Demographic Table that contains the 2010 Census population of that Neighborhood in the form of a whole decimal number. There is a numeric Population value for each and every row in the Demographic Table. An active relationship has been established between the two tables based on the Neighborhood Name value in each table. The cardinality and filter direction are both correct and the Neighborhood Names in the two tables match exactly.

I need a DAX formula to calculate that Per Capita value that respects the context of my pivot tables, i.e. contexts such as date ranges, aggregations of the neighborhoods into larger regions, the type of 311 Call, etc.

I can easily count the number of 311 Calls per neighborhood, but I'm having trouble calculating and applying the population denominator such that the Per Capita value is correct across all the various pivot table contexts. I suspect that the measure formula is really pretty simple, but using the various reference books available and the internet, I'm stumped.

Help. Thank you!
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Forum statistics

Threads
1,223,240
Messages
6,170,951
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top