Average with include total

[qv_testing]

Hi Community,

I have one scenario,  not sure if it is possible.

I have 3 columns, Key, N and Y - derived columns Y/N calculation. 

I have one row Total Average i.e we have to calculate based on column Average .

N total column average 770,607, same as rest averages.

Users wants to see in Text box Y/N column total average : 94.80%  (Please note here we have to include 1.126887975  on average )

Key	N	Y	Y/N
A	411,515	411,580	1.000157953
B	411,515	360,074	0.874996051
C	721,201	715,000	0.991401842
D	1,065,378	1,012,489	0.950355910
E	728,371	708,742	0.973051438
F	1,142,562	1,023,750	0.896012998
G	0	1,510,763	-
H	1,452,233	1,128,319	0.776954392
I	1,002,689	944,775	0.942240862
Total Average	770,607	868,388	1.126887975

Thanks in Advance!

[qv_testing]

Experts, any possibility ? 

[MayilVahanan]

Hi @qv_testing 

Try like below

=Num(Avg(Y) / Avg(N) /100 + Avg(Y/N) , '#,###.#%') gives 93.6%

Can you explain, how you got 94.8%.?

[chrismarlow]

Hi,

I am not sure if this makes any sense mathematically, but you can try variations on the below;

If(RowNo()=0,((sum(Y)/sum(N))+sum(aggr(Avg(Y/N),Key)))/(count(distinct Key)),Avg(Y/N))

Cheers,

Chris.

[qv_testing]

I'm sorry, Users were confused with requirement.

Thank you for your valuable time for response anyway.

Calculation is normal Average. It was done.