Forum Discussion

ehmacc's avatar
ehmacc
Frequent Visitor
6 years ago

Formula Help- Distinct count related to multiple criteria

Hi everyone- 

I am trying to solve for an issue and have worked at it for way too long, need to finally ask for help. I am working on turnover metrics which requires Average Active employees for a denomenator, and number of terminations as a numerator. Terminations is not a problem. Equation works when I include ALL employees, but if I want to show turnover by department, my denomenator does not update and so my % is way off. 

 

I am dealing with two tables. Table 1 is a DimDate table joined, 1 to Many, to Table 2, a demographic table with dates related to employees. 

Goal: Show an average count of active employees in a timeframe, and have this number react to filters for department.

Current state: I can show the average count of active employees in a timeframe, but the number does not react to the filters on the page/on the visual for department. I have built my formula as a column in my Date Term table and then use a Measure for my visual which is impacted by filters for date ranges.

Current formula "count unique IDs as active if their hire date is before this date and they are either still active or their term date was after this day":

Active Employees =

CALCULATE (DISTINCTCOUNT ( Employment[EmployeeNumber] ),

FILTER (Employment, Employment[LastHire] <= 'Date Term'[Date]

&& OR (Employment[TerminationDate] > 'Date Term'[Date],ISBLANK ( Employment[TerminationDate] ) = TRUE ())))

Measure- Average Active Employees = CALCULATE(average('Date Term'[Active Employees]))

 

Ex Table 1: 'Date Term'

DateFYFY-QuarterFY-PeriodActive Employees (calc above)
5/31/201920192019-42019-121100
6/1/201920202020-12020-11101

 

Ex Table 2: 'Employment'

EmloyeeNumberLastHireTerminationDateDepartment
1111/1/2017 Finance
2222/1/20058/1/2019Legal
Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Can you calculate Avg Employee like

    Total employee/Total Month.

    That my works better

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi ehmacc,

    If possible, could you please inform me more detailed information(such as your sample data and your expected output)? Then I will help you more correctly.

    Please do mask sensitive data before uploading.

    Thanks for your understanding and support.
    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • ehmacc's avatar
      ehmacc
      Frequent Visitor
      6 years ago

      dax 

      Links below. Easiest example would be using even just the emplyee count today, or the average this period. All company should be about 1025. But if I filtered it to Finance, I would expect 82, or 15 for HR. 

       

      As mentioned before- The Measure for Terminations as part of my Turnover calcultaion works and interacts with filters for Department and the Period/Quarter just fine; 

      Terms = CALCULATE(DISTINCTCOUNT(Employment[EmployeeNumber]), ISBLANK(Employment[TerminationDate]) = FALSE())

       

      Employment- https://drive.google.com/file/d/1ehwNoLXvNRpUu2DJuaKxp1q_sX-0g6Mt/view?usp=sharing

      DimDate- https://drive.google.com/file/d/16mYPQ8nbK7JLlp3cpPqwgmpqdIPsPVA5/view?usp=sharing

       

      Apologies for using Google Drive. My organization has our One Drive as internally shareable only. I also had to format a column on my DimDate query once I loaded to make the join work for both dates to have the same fomat- 

      JoinEmployment = FORMAT('Date Term'[Date], "MM-DD-YYYY")
      • dax's avatar
        dax
        Community Support
        6 years ago

        Hi ehmacc,

        Did you mean you want to get interactive based on date range slicer? If so, you could try below measure

         

        Measure =
VAR maxd =
    CALCULATE ( MAX ( 'Term Date'[Date] ), ALLSELECTED ( 'Term Date'[Date] ) )
VAR mind =
    CALCULATE ( MIN ( 'Term Date'[Date] ), ALLSELECTED ( 'Term Date'[Date] ) )
RETURN
    CALCULATE (
        COUNT ( Employment[Department] ),
        FILTER (
            Employment,
            Employment[Termination Date] < maxd
                && Employment[Termination Date] > mind
                || Employment[Termination Date] = BLANK ()
        )
    )

         

         If this is not what you want , please correct me and inform em your expected output.

        Please do mask sensitive data before uploading.

        Thanks for your understanding and support.
        Best Regards,
        Zoe Zhi

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.