4 Replies Latest reply on May 11, 2016 1:02 PM by David Carr

# Grand Total Averages

Hello,

I have attached a workbook with a simple data set. There are 3 ways to average the data at an aggregate level, two of which I am able to accomplish, but the 3rd is the one I am looking for.

As you will see in the attached:

1) Attainment % Calculation is simply the (sum of all credits) / (sum of all quotas).

2) Avg. Attainment is the average of each monthly attainment instance

3) This is the one I am trying to figure out. I want to sum credit & quota at the individual level, and then average the results for every person. So for instance John's total attainment over the 3 month period was 84.1889% and Lisa's was 1.170599%. I then want to average those two numbers and be able to view that at the regional (US) level. The correct answer I am looking for is 1.006244%.

Does anyone know how to create a calculated field for #3?

Any help would be greatly appreciated!!

David

• ###### 1. Re: Grand Total Averages

Hi David,

The following calculation will give you the result you are after:

avg(

{ FIXED [Rep Name] : [Attainment % Calculation]

}

)

1 of 1 people found this helpful
• ###### 2. Re: Grand Total Averages

Hi Peter,

This is great. One follow up question - this formula seems to lock in the time frame. Ie. once this formula is in place and I try to filter by month, the number is locked and stays the same.

Would you know how to make this dynamic so that it adjusts if you change your date filter?

Thanks!

David

• ###### 3. Re: Grand Total Averages

Hi David!

This is because FIXED expressions are applied before dimension filters. Click on the dropdown of the month pill you have in filter and 'add to context'. Here is an article that explains order of filter execution in Tableau:

• ###### 4. Re: Grand Total Averages

Great, thank you Pooja!