Calcuations

[Michalison]

I have this visual which is based on a teat sdlicer and Directorate slicer on an absence table - the calculation is Number of sick days divided by number of employees in the directorate  - when I select year and directorate the no of sick days are correct but the no of employees are wrong (the number of employees are fron the personnel_table) whcich has a 1 to many realtionship with the absence table based on Staff_Number

Total Sick Days = 'Calculation'[Count Sick Days]/'Calculation'[Absence FTE]

Absence FTE = CALCULATE(SUM(Personnel_Records[FTE]), USERELATIONSHIP(Absence[Start Date], 'Absence Date'[Date]))

This is giving me all the staff whatever directorate they are in so instead of 601 (absence days)/ 113 FTE I am getting 601/ 1053

Can anyone tell me where I am going wrong

[Ashish_Mathur]

Hi,

You should have a Calendar Table.  Does this work?

Absence FTE = CALCULATE(SUM(Personnel_Records[FTE]), USERELATIONSHIP(Absence[Start Date], 'Calendar'[Date]))

Hope this helps?

[amitchandak]

@Michalison , it should be the distinct count of employee id ?

like 

CALCULATE(distinctcount(Personnel_Records[Employee ID]), USERELATIONSHIP(Absence[Start Date], 'Absence Date'[Date]))

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Michalison]

Thanks that worked

[Michalison · Accepted solution]

Thank you for your reply I have used your suggestion and its looking better but still not quite right 

As you can see the absence FTE is now 70 when it should be 113 - I know what its doing its couting the FTE of those asbent from the resources department and not all people in the resources directorate I think the formula

CALCULATE(distinctcount(Personnel_Records[Employee ID]), USERELATIONSHIP(Absence[Start Date], 'Absence Date'[Date]))

perhaps the userelationship should not be based on the absence table I am not sure.   Many Thanks for your help

[v-chenwuz-msft]

Hi @Michalison,

You want to calculate number of all employees in the directorate?

Can you try allexcept(), allexcept() only allow the filter context you specify,maybe some measures like the following:

Absence FTE =
CALCULATE(
    COUNT( Personnel_Records[Employee ID] ),
    ALLEXCEPT( Personnel_Records, Personnel_Records[teat] )
)

It is hard to find what wrong without some sample data or pbix, if you need more help, please share some sample data or your pbix without sensitive data.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.