4 Replies Latest reply on Mar 10, 2016 8:05 AM by Jay Zen

# Average of Table Calculation

Hi,

I'm currently calculating a year-on-year change between two values using the following formula:

IF NOT ISNULL(SUM([Value]))

THEN (ZN(SUM([Value])) - LOOKUP(ZN(SUM([Value])), -1)) / LOOKUP(ZN(SUM([Value])), -1)

END

This produces the correct result (going across the table). However, no matter what I try (whether using totals or other calculations), I cannot seem to get a correct average of all of the resulting outcomes.

Tableau either calculates nothing, or first calculates an average of [Value] for each year, which it then calculates a year-on-year change on. What I want is an average of just the percentages that result from the above calculation for each entry in my data.

Thanks for your help,

Jay

• ###### 1. Re: Average of Table Calculation

Hi Jay:  Could you mock up/post an example TWBX using Superstore Sales?   Its difficult to answer without having a sample to play with -- but I'll be happy to take a look when i have a moment, if you can post a sample.  Cheers!

• ###### 2. Re: Average of Table Calculation

An example is attached as requested - thank you for taking the time to look at this for me!

Note that the summary card average (on sheet 2) is what I'm after.

• ###### 3. Re: Average of Table Calculation

You can wrap the whole thing in a WINDOW_AVG() : Not sure what sort of table layout you are really going for, but that calc will give you the average of the table calc.

--Shawn

• ###### 4. Re: Average of Table Calculation

Hi Shawn,

Thanks for your help but that's not quite what I'm after. Sheet 1 is there primarily to illustrate how I arrive at the individual year-on-year calculation.

What I need to calculate is an average of all of the people's increases. In this instance it would be an average of 2% for Alllan, 2% for Ed, 2% for John and so on, with a correct final value of 3.31% (the average in the summary on sheet 2).