6 Replies Latest reply on Jul 17, 2017 5:41 PM by Murong He

# Average of Distinct Values

Hello,

I am trying to create a calculated field that shows the average distinct count of [Case ID] by Employee for each group (see formula below). The AVG function does not give me the right number -- it calculates the value for each row (an employee can have many rows associated to his/her ID). I think that something like Window_Average would work, but when I tried replacing AVG with Window_Avg, I got an error. Any other ideas? Thank you!

{fixed[Group ID]: AVG({fixed[Employee ID]:COUNTD([Case ID])})}

• ###### 1. Re: Average of Distinct Values

Can you share a sample workbook? Your LOD looks close to what you want ... the win_avg won't work with LODs (can't have table calc with LODs)

• ###### 2. Re: Average of Distinct Values

What you are trying to do is, you are aggregating an aggregating and Fixing it across Group. So  this formula you wrote would return that. What are you trying to achieve. Can you attach a workbook?

• ###### 3. Re: Average of Distinct Values

Hello,

I am not able to share a notebook due to the confidentiality of the data, but below are some dummy data that illustrates the issue I'm having. The Red is how I believe Tableau is calculating the answer based on the formula (the average count of CaseID based on Group) while the green is what is the desired value (the average count based on both Employee ID and Group). Basically, I only want one value from each Group/EmployeeID combo to be taken into account for the calculation. Thank you!!

• ###### 4. Re: Average of Distinct Values

Hello,

I am not able to share a notebook due to the confidentiality of the data, but below are some dummy data that illustrates the issue I'm having. The Red is how I believe Tableau is calculating the answer based on the formula (the average count of CaseID based on Group) while the green is what is the desired value (the average count based on both Employee ID and Group). Basically, I only want one value from each Group/EmployeeID combo to be taken into account for the calculation. Thank you!!

• ###### 5. Re: Average of Distinct Values

Hi Murano,

Check Screenshot and attached.

Hope it Helps!!!

Let me know.

Thanks

Deepak

• ###### 6. Re: Average of Distinct Values

Yes, that's exactly it! Thank you!!!

Calculated Field:

({FIXED [Group ID]: COUNT([Case ID])}) / ({FIXED [Group ID]: COUNTD([Employee ID])})