Calculating True AVG of column with same value

ColinHaze

Hi,

Am I missing something or misusing something? I have this beastmode to calculate the True average of a column called Days_In_Service, however it just appears to be adding up the distinct values and not dividing them. If the Unit# is the same then the days_in_service will be the same. I am trying to avoid the situation where if the days_in_service are the same but have different unit# then the days_in_service wont be counted due to using distinct.

Any help is appreciated, Thank you!

Here is the beastmode. The output for this beastmode is 29,150 instead of 14,575.

SUM(DISTINCT Days_In_Service) OVER (PARTITION BY Unit#) / COUNT(DISTINCT Unit#)

Here is a snippet of my data.

ColemenWilson

SUM(MAX(Days_In_Service) FIXED(BY Unit#)) / COUNT(DISTINCT Unit#)

ColemenWilson

SUM(MAX(Days_In_Service) FIXED(BY Unit#)) / COUNT(DISTINCT Unit#)