Forum Discussion

NickSirTek's avatar
NickSirTek
Frequent Visitor
2 years ago
Solved

Employee head Count and Slicers

Hi Everyone,   I am new to PowerBI and slowly learning.   I have setup sharepoint to house monthly excel files that contain employees for that month. I created a fiscal information table which i...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    i think you should not count date. You should distinct count something like employee ID or employee name

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
2 years ago

NickSirTek 

pls provide some sample data and expected output

  • NickSirTek's avatar
    NickSirTek
    Frequent Visitor
    2 years ago

    Thank you Ryan

     

    ex:
    headcount for 2025/Q1/JULY was 456

    headcount for 2025/Q1/AUG was 438

    when I select the slicer to show 2025/Q1 the headcount is wrong, it adds both july and aug together. Each file I add every month has duplicate users so its almost like I need to filter by the DataDate column while doing the count x since the date is required.

    I have attached some screenshots of my visuals and the results

    I want to see the average employee headcount only when Quarter or year is select and the visual to display accordingly.






    • ryan_mayu's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      2 years ago

      If you don't want the duplicated users, then you can use DISTINCTCOUNT to remove duplicates.

      • NickSirTek's avatar
        NickSirTek
        Frequent Visitor
        2 years ago

        thank you

         

        I was able to get the employee head count for months and it works, this formula also does the average for quarter and yearly..seems to be working.

        Head Count =
        VAR EmpCount = COUNTX('Employee Data','Employee Data'[DataDate])
        VAR  AverageCount = DISTINCTCOUNT('Employee Data'[DataDate])
        RETURN
            DIVIDE(
                EmpCount,
                AverageCount,
                0
            )
         
         
        my next question is, how do I get this formula to filter items based on the slicer..so if I select quarter or year.. i want this formula below to display based on that filter
         
        Age Groups =
        IF ( [Age] <= 19 && [Age] >= 0 , "< 20 Years",
        IF ( [Age] <= 29 && [Age] >= 20 , "20 to 29 Years",
        IF ( [Age] <= 39 && [Age] >= 30, "30 to 39 Years",
        IF ( [Age] <= 49 && [Age] >= 40, "40 to 49 Years",
        IF ( [Age] >= 50, "50 > Years",
        "")))))