Average of a Distinct Count - DAX needed

[jl20]

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!

[Sean · Accepted 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
)