Calculating a normalization by dividing by count in another table

Hello, I haven’t been able to find a similar solution on the boards so I apologize if this has been asked before. I’m working at a health insurance company and often the way that we normalize categories of expenses is using a metric type called PMPM or "Per Member Per Month.

We have a claims fact table that has a record per claim, the claim can be categorized by many dimensions of course. However, when calculating a sum of cost after some grouping by month, then we want to divide by the number of members that were enrolled in that month so that the metric becomes sum of my claim category per month, divided by number of members enrolled in that month to yield the cost in that category per member per month.

Then in more nuances cases we also want to calculate “total cost per utilizing members per month” where instead of just dividing by the total number of members enrolled, we divide by the total number of members utilizing the service.

Some notes about our data objects:

• 
  claim_fact table that has claims with categories, and a link to member_month


• 
  member_month_fact table has one record per member per month. If we sum up the number of rows per member, then we get the total enrollment for that month.


• These are related by joining on <member id> and <month>
  

Thanks!