Forum Discussion

Michalison's avatar
Michalison
Icon for Helper II rankHelper 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 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

 

 

 

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

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper 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
      Icon for Helper II rankHelper 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
        Icon for Community Support rankCommunity 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.

         

         

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    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?