6 Replies Latest reply on Oct 11, 2018 4:09 AM by paolo.raia

Average of a row of averages

Hi,

I have the following table which shows an average total per month by letter code (A. B. C). I would like to add an average column at the end which shows an average of the averages, i.e. for code A this will be: (23+48+19+35) / 4 (months will change based on selection filter).....

Am trying LOD'S, WINDOWS_AVG etc but not getting anywhere fast.

Many thanks in advance.

• 1. Re: Average of a row of averages

hi Paolo,

So I think you can achieve this by adding a Grand Total to rows, and then changing the aggregation level of the Grand Total

Hope that does the trick

• 2. Re: Average of a row of averages

Hi Simon,

Thanks for your time and replying.

I did previously try using your Grand Total method on my table, but the figures were not calculating correctly.

I do see that you used SUM(Perce..) in Text.  Can you please tell me if this was a calculated field of some sort?

Many thanks and regards.

• 3. Re: Average of a row of averages

hi Paolo,

No it's not calculated. As I don't know what your data is...all I have is the 12 values you've given I just created a datasource with those values. Tableau will (by default) bring a measure in with a SUM aggregate, but as it only represents one row, it would the same value even if I'd used MIN, MAX, AVG...or any other aggregation.

If you can post an example, which better represents the level of your actual data and how you get to the %age (from the lower level) I can have a look.

• 4. Re: Average of a row of averages

Hi Simon,

I've attached a sample workbook so that I can explain a little better as you are quite right requesting this!

Tabs included:

Raw Data (data source)

Totals (overall Code totals by month)

% Code by Month (shows % of each code split by month, down)

Average % of Code (here, I am wanting to see an average of each code)......

So A will be 49.25+59.57+67.35+70.90 / 4 = 61.77%

Thanks again!

• 5. Re: Average of a row of averages

So oddly using the Grand Total on the Table Calculation version of Share of Sales doesn't quite give the "right" answer (right in what you expect). It's close but not quite. In short this is because GTs are computed separately at a higher level of aggregation than the viz (so the weighting at a total level is different from taking the average of each of the month/code elements)

To get round this we can use an LoD to compute the Share of Records like so

[%age of Records - LoD]

SUM([Number of Records])

/

SUM({FIXED DATETRUNC('month',[Date Complete]): SUM([Number of Records])})

And then when we use this field instead, and use the GT aggregation type of AVG we get the right results. Just a word of caution a FIXED LoD is computed before any regular filters are applied, so if you have filtering that you want to affect the LoD you need to make that filter an "in context" filter, which means the filter will be applied before the LoD is computed.

Hope that helps

2 of 2 people found this helpful
• 6. Re: Average of a row of averages

Morning Simon,

Many thanks for your concise explanation of this.

It has helped me immensely and has resolved my issue.

Have a great day!

Regards.

* Added comment: I cannot seem to Add Context to a filter from a secondary data source.  Is there a workaround for this?