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

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",
    "")))))
    • ryan_mayu's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      2 years ago

      can we create a calucated column for that  age group?

      • NickSirTek's avatar
        NickSirTek
        Frequent Visitor
        2 years ago

        so I have a calculated colum called Age group and a column that has ages
        the formula is:

        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",
        "")))))


        I attached a screenhot of the calculated column with the required information.

         

        To display it, I greated a bar chart and here is the info for that

        if I select y-axis to show distinct count it shows 1 count for each age group.