Forum Discussion

jl20's avatar
jl20
Icon for Helper IV rankHelper IV
9 years ago
Solved

Average of a Distinct Count - DAX needed

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 NumberDate WorkedEmployee #
2652710/1/2015Employee 1
2652710/2/2015Employee 1
2826610/1/2015Employee 2
2929610/1/2015Employee 2
2801210/2/2015Employee 2
2770810/5/2015Employee 2
2826610/5/2015Employee 2
2635310/1/2015Employee 3
2680310/1/2015Employee 3
2680310/2/2015Employee 3
2680310/3/2015Employee 3
   
Desired Results: 
EmployeeAverage # of Distinct Jobs per DayDays Worked
Employee 11.02
Employee 21.73
Employee 31.33

 

Thanks in advance for your help!

Need Help
  • Sean's avatar
    Sean
    9 years ago

    This should give you the desired output :smileyhappy:

    Average # of Distinct Jobs per Day =
DIVIDE (
    CALCULATE (
        COUNTA ( 'Table'[Job Number] ),
        ALLEXCEPT ( 'Table', 'Table'[Employee #] )
    ),
    DISTINCTCOUNT ( 'Table'[Date Worked] ),
    0
)

     

1 Reply

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    9 years ago

    This should give you the desired output :smileyhappy:

    Average # of Distinct Jobs per Day =
DIVIDE (
    CALCULATE (
        COUNTA ( 'Table'[Job Number] ),
        ALLEXCEPT ( 'Table', 'Table'[Employee #] )
    ),
    DISTINCTCOUNT ( 'Table'[Date Worked] ),
    0
)