All Except Year

[Anonymous]

Hi, 

I am trying to display the total distinct employees working per day per year, but my All Except formula displays the Total days instead of separating out the days each year...

The formula below is the sum of the distinct count of employees per day:

** Emp Test = sumx(values('CMPL Table'[Date Day]),calculate(distinctcount('CMPL Table'[Employee Name])))

The formula below is what I googled to get the total to be displayed for each year:

**Employees/Day Total = calculate([** Emp Test],allexcept('CMPL Table','CMPL Table'[Employee Name])

I am not sure how to separate out the year because if i change the Employee Name to Year in the second formula, it gives the total instead of being able to filter out by Employee Name

[sevenhills · Accepted solution]

If this is true, that you are looking for count of distinct combinations such as 

     count( DISTINCT Date_Day, Employee_Name) from CMPL table

In my view, I will achive using Summarize and CountRows

Emp And Date unique values = COUNTROWS (

           Summarize ( 'CMPL Table', 'CMPL Table'[Date Day], 'CMPL Table'[Employee Name])

)

[Anonymous]

So first I created a formula using the advice you sent me...:

***Employees/Day Total = countrows(SUMMARIZE('CMPL Table','CMPL Table'[Employee Name],'CMPL Table'[Date Day]))

From there I created a final formula that displayed the totals correctly:

**Employees/Day Total = calculate([***Employees/Day Total],allexcept('CMPL Table','CMPL Table'[Employee Name],'CMPL Table'[Year]))

Thank you again!
Sarah

[Anonymous]

This worked! i just had to create another formula thank you!

[sevenhills]

Glad it worked. Please share the formula to the group...

[Anonymous]

@sevenhills Thank you!
This result does not give me the subtotal for each year for each row though... these are the formulas i used below:

** Emp Test = sumx(values('CMPL Table'[Date Day]),calculate(distinctcount('CMPL Table'[Employee Name])))

**Employees/Day Total = calculate([** Emp Test],allexcept('CMPL Table','CMPL Table'[Employee Name]))

***Employees/Day Total = countrows(SUMMARIZE('CMPL Table','CMPL Table'[Employee Name],'CMPL Table'[Date Day]))

*Stevenson Day2 is the result that i am looking for

Thank you!
Sarah