MikeDavisSQL

In a lot of industries there is a popular calculation called “Per Member Per Month Per 1000” calculation or the “Per Customer Per Month Per 1000” calculation. This is used to determine the level at which the company is rendering services compared to the number of company customers. This calculation is sometimes called the 1000pmpm calculation for short.

In this article you will be looking at the member count numbers at no lower than the month level. Here is an example of the 1000pmpm calculation. If you are looking at the year level the number of months would be 12 so it would be 12 divided by 12 which is 1. So it can be removed from the calculation at the year level. This will be important later on.

[Auth Count] x ((12 / [number of months]) x 1000) / [Member Count]

In this example you will be looking at a health care company. The main number the company wants to look at is the number of authorizations per 1000 members. The data warehouse has a fact table with all of the authorizations. There is also another fact table with the member count on it also. The Fact Authorization table also has three date columns, Admit date, Discharge Date, and Date Received. This will be important when calculating the numbers at different levels of the date Hierarchy. The Fact table also has many other dimension surrogate keys mapped to the appropriate dimensions.

The Fact Membership table has a date on it named incurred date. This date will have a relationship to all three dates on the fact authorization table. The reason for this multiple date relationships is due to the users wanting to see the differences in discharge and admit numbers.

There are several other dimensions mapped to each fact table. In this example you will ignore all other dimensions except the date dimensions. But keep in mind those other dimensions need to have relationships set up to be able to slice the numbers by those dimensions.

Here is an example of part of the membership fact table. Notice that the incurred date repeats. This is because it depends on which dimensions you slice on which member count you will get. This also needs to be summed up to the month level. After the month level it is no longer valid as a sum. This seems tricky but can be done with a calculation. The original member count measure is set to sum on the measure properties. This measure is set to hidden also because the sum is invalid over the month level.

To fix the summing issue you will need to create a calculation to divide up the member count by the number of month. Here is that calculation:

[Member Count]:

Case

When [Date Received].[Date Hierarchy].currentmember.level is

[Date Received].[Date Hierarchy].[Year]

Then [Measures].[Members] / 12

When [Date Received].[Date Hierarchy].currentmember.level is

[Date Received].[Date Hierarchy].[Quarter]

Then [Measures].[Members] / 3

When [Admit Date].[Date Hierarchy].currentmember.level is

[Admit Date].[Date Hierarchy].[Year]

Then [Measures].[Members] / 12

When [Admit Date].[Date Hierarchy].currentmember.level is

[Admit Date].[Date Hierarchy].[Quarter]

Then [Measures].[Members] / 3

When [Discharge Date].[Date Hierarchy].currentmember.level is

[Discharge Date].[Date Hierarchy].[Year]

Then [Measures].[Members] / 12

When [Discharge Date].[Date Hierarchy].currentmember.level is

[Discharge Date].[Date Hierarchy].[Quarter]

Then [Measures].[Members] / 3

Else

[Measures].[Members]

End

In this calculation you can see the case statement is just dividing the member count by 12 at the year level and 3 at the quarter level. The member count is not valid below the month level so this takes care of all the possibilities. You will also notice that the calculation includes all three of the date hierarchies. This ensures the calculation works in all date dimensions. This can be shown on the reports and will show the correct member count for every level in all the date dimensions. The below image is of the member numbers with just the summation and the other after the above calculation. By dividing with the number of months we get the average instead of the sum at the quarter and year level, which is what we want.

The next calculation is just to divide the member count by 1000. This can also be shown on the reports at all date levels.

[Member Count / 1000]:

iif([Measures].[Member Count] <= 0,

null,

[Measures].[Member Count] / 1000)

The last calculation we need to do to the member count is used in the actual calculations where you divide the measures by the member count. In this example you will use the Authorization count. But instead of multiplying every measure by a number then dividing by the member count, you can do a little math and figure out that you just divide the member count number and then use this one number for all measures.

[MemberCount / 1000 for Calcs]:

Case

When IsEmpty([Member Count / 1000])

Then Null

When [Date Received].[Date Hierarchy].currentmember.level is

[Date Received].[Date Hierarchy].[Year]

Then [Member Count / 1000]

When [Date Received].[Date Hierarchy].currentmember.level is

[Date Received].[Date Hierarchy].[Quarter]

Then [Member Count / 1000] / 4

When [Admit Date].[Date Hierarchy].currentmember.level is

[Admit Date].[Date Hierarchy].[Year]

Then [Member Count / 1000]

When [Admit Date].[Date Hierarchy].currentmember.level is

[Admit Date].[Date Hierarchy].[Quarter]

Then [Member Count / 1000] / 4

When [Discharge Date].[Date Hierarchy].currentmember.level is

[Discharge Date].[Date Hierarchy].[Year]

Then [Member Count / 1000]

When [Discharge Date].[Date Hierarchy].currentmember.level is

[Discharge Date].[Date Hierarchy].[Quarter]

Then [Member Count / 1000] / 4

Else

[Member Count / 1000] /12

End

This last calculation may be confusing. To help clarify this, take a look at the 1000pmpm calculation again:

[Auth Count] x ((12 / [number of months]) x 1000) / [Member Count]

What you are basically doing is moving the ((12 / [number of months]) to the bottom of the calculation and dividing the member number by this answer. This ensures the company can compare the number across any level. So the quarter level should be very close to the month and year level. The last calculation makes sure this happens by dividing member count by the number of quarters in a year at the quarter level. At the year level it would be 12 divided by 12, which equals 1 so no division is needed. That final else is used to get all the months.

Now you can use this final calculation to divide all the measures in the cube. The below calculation can be applied to all measures. This calculation is checking for divided by zero errors then just dividing the measure by the member number we have worked hard to get above.

iif([Measures].[Member Count / 1000] <=0,

Null,

[Measures].[Auth Count] / [MemberCount / 1000 for Calcs])

Here you can see the final results:

By using this method you can use this final member count number easily and save lots of unnecessary work. If you try to calculate each measure separately all of the calculations would have been massive.