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

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.

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

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