Power Pivot Dax Formula to calculate Population Per Capita Value

Joe Fowler

New Member
Dec 3, 2011
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!

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics