cancel
Showing results for
Did you mean:  Post Patron

All Except Year

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

1 ACCEPTED SOLUTION  Solution Sage

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])

)

5 REPLIES 5  Solution Sage

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])

)  Post Patron

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  Post Patron

This worked! i just had to create another formula thank you!  Solution Sage  Post Patron @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  