cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sk15227
Post Patron
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
sevenhills
Solution Sage
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])

)

View solution in original post

5 REPLIES 5
sevenhills
Solution Sage
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])

)

View solution in original post

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

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

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

 

Capture.PNG

 

@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
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!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

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

Top Solution Authors