Create a Measure: Cost Per Employee

[Anonymous]

Hi,

I'm trying to create a new measure that will calculate the healthcare "Cost Per Employee".

I'm trying to create this as a measure that I can include in several tables and graphs.  This needs to calculate on a monthly basis since I will be plotting it that way.  I have several slicers as well so it needs to be dynamic that way as well.

In the simplest form; the formula is:  (# of Employees) divided by (Plan Paid):

• In order to find the (# of employees) I created a measure:
  • Employee Count Claims = CALCULATE(DISTINCTCOUNT('Claim Data Pull'[Member ID]), FILTER('Claim Data Pull','Claim Data Pull'[Relation]="Employee"))
• The (Plan Paid) amount is a column/field in the table already.  The field is:  'Claim Data Pull'(Paid)

My attempted solution was:

 = DIVIDE(CALCULATE(DISTINCTCOUNT('Claim Data Pull'[Member ID]), FILTER('Claim Data Pull','Claim Data Pull'[Relation]="Employee")), SUM('Claim Data Pull'[Paid]))

Can you assist me in resolving the issue?  Currently my results are 0:

[Anonymous · Accepted solution]

I beleive you have your DIVIDE function flipped as I believe you want to divide the cost by the count of employees. Also, consider using the "alternative result" allowed in the DIVIDE syntax: https://docs.microsoft.com/en-us/dax/divide-function-dax

If you can post a sample of your data (using proxy data as we of course would not wish for you to disclose personal employee data) we would potentially be able to help you more.

If this helps please kudo.

If this solves your problem please accept it as a solution.

[Anonymous]

Perfect thank you.