Count % with Date Slicer

DAX DAX Calculations
1

Hello,

I am trying to count percentage of Employees who are Bilingual with a Date Slicer. I am attaching a copy of the report here. For the Bilingual part, I have the following calculation that is causing an error. Would you be able to explain me what I might be doing wrong in here? Thank you

Bilingual % =

CALCULATE(

DIVIDE(CALCULATE(COUNT(tEmployee[Bilingual]),tEmployee[Bilingual]=“Y”),(CALCULATE(COUNT(tEmployee[Bilingual]),ALLSELECTED(tEmployee[Bilingual]))))

,

AND(Dates[Date] >= MAX(tEmployee[Enter Date]),

Dates[Date] <= MIN(tEmployee[Leave Date])))

eDNA - Numbersenrolled (1).pbix (135.7 KB)

2

Hello @supergallagher25,

Thank You for posting your query onto the Forum.

I’m providing two formulas below about how to count the “Total No. of Employees” as well as “Total No. of Bilingual Employees”.

Total No. of Employees = 
CALCULATE( DISTINCTCOUNT( tEmployee[EmpID] ) ,
    FILTER( VALUES( tEmployee[Enter Date] ) , tEmployee[Enter Date] <= MAX( Dates[Date] ) ) ,
    FILTER( VALUES( tEmployee[Leave Date] ) , tEmployee[Leave Date] >= MIN( Dates[Date] ) ) )

Total No. of Bilingual Employees = 
CALCULATE( DISTINCTCOUNT( tEmployee[EmpID] ) ,
    FILTER( VALUES( tEmployee[Enter Date] ) , tEmployee[Enter Date] <= MAX( Dates[Date] ) ) ,
    FILTER( VALUES( tEmployee[Leave Date] ) , tEmployee[Leave Date] >= MIN( Dates[Date] ) ) ,
    FILTER( VALUES( tEmployee[Bilingual] ) , tEmployee[Bilingual] = "Y" ) )

Below is the screenshot provided for the reference -

Total No. of Employees and Bilingual Employees
Total No. of Employees and Bilingual Employees1436×787 141 KB

I’m also attaching the PBIX file of my working for the reference.

Hoping you find this useful and helps you in your analysis. :slightly_smiling_face:

Thanks & Warm Regards,
Harsh

eDNA - Numbersenrolled (1).pbix (139.0 KB)

3

Hello @supergallagher25,

My apologies, actually I forgot to add the formula to calculate the % of Bilingual Employees. Below is the formula provided for the reference -

% of Bilingual Employees = DIVIDE( [Total No. of Bilingual Employees] , [Total No. of Employees] , 0 )

I’m providing a link of a video from Business Analytics Series where Sam has explained the similar concept.

Thanks & Warm Regards,
Harsh

eDNA - Numbersenrolled (1).pbix (139.3 KB)

4

Hi @supergallagher25, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!