3 Replies Latest reply on May 3, 2017 10:12 AM by Shinichiro Murakami

# Grand Total Average - Table Calculation

Hi There -

In the screenshot below, I have created a "% to max calculation" field calculating the % of maximum for each individual, by week. *Individuals have been whited out*

Here are my formulas for Max Call, as well as % to Max

Max Call

% to Max

The problem I am running into is the grand totals. I need to the grand total to calculate the average of the above averages for each individual, but right now it is not doing that. For instance, in week 1, the grand total should be ((0% + 2.67% + 0%) / 3), week 2 ((1.76% + 5.33% +0%)/3) and so forth.

Any ideas how to fix this?

Thank you!!

• ###### 1. Re: Grand Total Average - Table Calculation

Hi David

To get G.Total in table calculation is quite a bit troublesome.

Create Union to duplicate data.

[Call 2]

if attr([Table Name])="Sheet1_flat" then sum([Call]) else window_sum(sum([Call]))/2 end

[Max call 2]

if attr([Table Name])="Sheet1_flat" then [Max Call]

else window_max([Call 2]) end

[% to Max2]  // because of duplicated data, nneds to be divided by 6.

if attr([Table Name]) ="Sheet1_flat" then [% to Max] else window_sum([% to Max])/6 end

if [Table Name]="Sheet1_flat" then [Group] else "G.ttl" end

[Filter hide]

[Table Name]+[Group]

Final Result

And one more thing.

G.Total's behavior is different from the version, so the solution might be different if you are using other version.

Thanks,

Shin

• ###### 2. Re: Grand Total Average - Table Calculation

Thank you for the very thorough explanation Shin, this worked perfectly, thank you for your help!

David

• ###### 3. Re: Grand Total Average - Table Calculation

You are welcome and I'm little bit surprised you could do it only by one instruction.

It's pretty complicated, good job David..

Thanks,

Shin