Percentage of Employees

DAX DAX Calculations
1

Hello,

I am trying to calculate the percentage of employees in Branch with a date slicer. My original data is a lot bigger and has duplicates. Also, I cannot used the grand total here since I will be needing it for another calculation afterwards.

My formula to calculate the percentage of employees  is: 
% of Employees =
CALCULATE(DIVIDE

(DISTINCTCOUNT(tEmployee[EmpID]),

CALCULATE(DISTINCTCOUNT(tEmployee[EmpID]), ALLSELECTED(tEmployee))),

FILTER(VALUES(tEmployee[Enter Date]),tEmployee[Enter Date] <= MAX(Dates[Date])),

FILTER(VALUES(tEmployee[Leave Date]),tEmployee[Leave Date] >= MIN(Dates[Date])))

image %Employees.pbix (131.5 KB)

However, it is not bringing in the correct percentage. Both the branc names and dates are a slicer here and users will be able to select all of the brances or just one branch at a time. I am attaching the file below. Please let me know what I might be doing incorrectly here.

Thank you

2

Hi @supergallagher25,

Thanks for providing a PBIX file :+1:
I would suggest using measure branching.

% of Employees  v2 = 
    DIVIDE( 
        CALCULATE( [Employees], ALLSELECTED( Dates )),
        CALCULATE( [Employees], 
            ALLEXCEPT( tEmployee, tEmployee[Branch Name] )
        )
    )

I hope this is helpful.

1 Like
3

Thanks for posting your question @supergallagher25. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

4

Hello,
I have tried to solve it for you,
I created three measures, first i calculated Total Employees by using distinct count,
then total employees by ignoring all the filters using all(named it as Total Employees (W) as it will always give total number of employees ignoring all the filters) and then we can use it late to find percentage.
Then i created a measure to calculate Total employees in each branch and.
Finally i calculated the percentage by dividing Total Employees(W) and Total Employees in branch.
then in visuals i created table which is shoeing percentages and slicers are also working.
date slicer was not working initially as you have two relationships between your date and employees table and they both are inactive, so you have to choose one relationship as active so that date slicer can work.
I am attaching solved Pbix file and some screenshots.
[%Employees By Branch And Date.pbix|attachment]
(upload://xR2Z2172giaMUvg6Gd2sPLXlqdN.pbix) (132.4 KB)

screenshot 2
screenshot 21018×640 32.8 KB

image
image1366×768 162 KB

%Employees By Branch And Date.pbix (132.4 KB)

it may not be the best solution but as per my perception, it should work.

1 Like
6

Hi @supergallagher25, did the response provided by the contributors help you solve your query? If not, how far did you get, and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

7

Thank you.