Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I'm looking to write a measure that calculates the average number of jobs an employee works per day (they may work one or more than one job per day). I'm not interested in days where they don't work (i.e., the average should only include dates in which they work). It will be easiest to just show you what I'm trying to achieve with some sample data. Looking for the code that will get me the bolded results below.
Dataset: | ||
Job Number | Date Worked | Employee # |
26527 | 10/1/2015 | Employee 1 |
26527 | 10/2/2015 | Employee 1 |
28266 | 10/1/2015 | Employee 2 |
29296 | 10/1/2015 | Employee 2 |
28012 | 10/2/2015 | Employee 2 |
27708 | 10/5/2015 | Employee 2 |
28266 | 10/5/2015 | Employee 2 |
26353 | 10/1/2015 | Employee 3 |
26803 | 10/1/2015 | Employee 3 |
26803 | 10/2/2015 | Employee 3 |
26803 | 10/3/2015 | Employee 3 |
Desired Results: | ||
Employee | Average # of Distinct Jobs per Day | Days Worked |
Employee 1 | 1.0 | 2 |
Employee 2 | 1.7 | 3 |
Employee 3 | 1.3 | 3 |
Thanks in advance for your help!
Solved! Go to Solution.
This should give you the desired output
Average # of Distinct Jobs per Day = DIVIDE ( CALCULATE ( COUNTA ( 'Table'[Job Number] ), ALLEXCEPT ( 'Table', 'Table'[Employee #] ) ), DISTINCTCOUNT ( 'Table'[Date Worked] ), 0 )
This should give you the desired output
Average # of Distinct Jobs per Day = DIVIDE ( CALCULATE ( COUNTA ( 'Table'[Job Number] ), ALLEXCEPT ( 'Table', 'Table'[Employee #] ) ), DISTINCTCOUNT ( 'Table'[Date Worked] ), 0 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
98 | |
80 | |
67 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |