Forum Discussion

Michalison's avatar
Michalison
Helper II
4 years ago
Solved

Calcuations

  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 ...
Need Help
  • Michalison's avatar
    Michalison
    4 years ago

    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

amitchandak's avatar
amitchandak
Super User
4 years ago

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's avatar
Michalison
Helper II
4 years ago

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's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    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.