Showing results for 
Search instead for 
Did you mean: 
Post Patron
Post Patron

Sum of Distinct Count of Employees per Day



I am trying to create a measure or column that displays the total number of distinct employees working each day for each of the past 2.5 years. I created a work-around formula:

Employees/day = calculate(DISTINCTCOUNT('Table'[*Employees])-1,filter('Table','Table'[Date Day]=earlier('Table'[Date Day])))
but this does not allow me to filter on a specific employee when I try to create a table, since my measure only shows the total number of employees per year..
SUMX of the distinctcount of employees also does not work because I want to display the overall number of employees by each type of description. If I use SUMX, the calculation filters based on the description and does not give the total employees for each description.
Is there another formula I can use to allow me to display the total employees for different descriptions but also allow me to filter for how many days a specific employee worked/year?
Please let me know if there is anything I can clerify 
Thank you!

Hi @amitchandak , this gave me 0s as all the values...


Thank you!

View solution in original post

Super User
Super User

@sk15227 , Try a measure like


sumx(values('Table'[Date Day]), calculate(DISTINCTCOUNT('Table'[*Employees])))




sumx(values('Table'[Date Day]), calculate(DISTINCTCOUNT('Table'[*Employees])-1))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Hi @amitchandak , this gave me 0s as all the values...


Thank you!

Helpful resources

2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors