cancel
Showing results for
Search instead 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

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

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

## Helpful resources

Announcements

#### Check it Out!

Click here to read more about the December 2021 Updates!

#### Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

#### Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors