2 Replies Latest reply on Oct 3, 2018 6:17 AM by Elizabeth Perry

    Count distinct across months and years

    Elizabeth Perry

      HI, I have transactional level expenses data which shows individual expenses claimed by an employee each row shows the employee, the date claimed, the amount and the employees manager. I provide yearly totals at manager and employee level and have been asked to show the total number of individuals under each manager. I've been experimenting with countd() but I run into problems when I try to add an if statement to this, for example IF ([Year] = '2018' THEN COUNTD([EmployeeID]), it tells me I can't mix aggregate with non aggregate. I don't want to show all measures per year so I have created calculated fields for expenses for 2017 & 2018 I've then created a field to calculate the difference and the variance between years. I want to be able to do the same for total employees. I've created a very basic example of the data I'm dealing with, each employee can appear under different managers as time passes. Obviously if I total distinct employees at manager level in my example I'd want to see a total per 2017 & 2018 but then I'd also want it to react to the month filter I have on the workbook. TIA.