Total for All Rows

[#1]

Hi,

I am trying to create summary table based on the below data using Aggr() function however i do not want to show the dimension.

Data table -

Agncy Name	MTD	Full Month PY	PY MTD	Full Month Projn
	£7.2	£8.2	£5.9	£9.2
ABC12345	£0.3	£0.4	£0.3	£0.4
ABC12346	£1.4	£1.1	£0.8	£2.0
ABC12347	£0.2	£0.2	£0.1	£0.2
ABC12348	£0.0	£0.1	£0.0	£0.0
ABC12349	£0.3	£0.0	£0.0	-
ABC12350	£0.0	£0.0	£0.0	£0.0
ABC12351	£0.0	£0.0	£0.0	£0.0
ABC12352	£0.1	£0.2	£0.1	£0.1
ABC12353	£0.4	£0.5	£0.4	£0.5
ABC12354	£2.1	£3.6	£2.7	£2.8
ABC12355	£1.7	£1.1	£0.8	£2.4
ABC12356	£0.0	£0.1	£0.0	£0.1
ABC12357	£0.0	£0.0	£0.0	£0.0
ABC12358	£0.0	£0.0	£0.0	£0.0
ABC12359	£0.0	£0.0	£0.0	£0.0
ABC12360	£0.0	£0.0	£0.0	£0.0
ABC12361	£0.0	£0.1	£0.1	£0.1
ABC12362	£0.3	£0.4	£0.3	£0.4
ABC12363	£0.1	£0.1	£0.1	£0.1
ABC12364	£0.3	£0.0	£0.0	-
ABC12365	£0.0	£0.1	£0.1	£0.0
ABC12366	£0.0	£0.1	£0.1	£0.0
ABC12367	£0.0	£0.1	£0.1	£0.0

The condition to calculate the Full Month Projn column is

=if(PY MTD >0, (MTD*Full Month PY)/PY MTD,MTD)

and the desired output is -

MTD	Full Month PY	PY Mnth To Date	Full Month Projn
£7.2	£8.2	£5.9	£9.8

Can someone help me with this please?

Regards,

[#2]

Something like this -

MTD	Full Month PY	PY Mnth To Date	Full Month Projn
£7.2	£8.2	£5.9	£9.8

Column Full month Projn is a based on below condition -

=if(PY MTD >0, (MTD*Full Month PY)/PY MTD,MTD)

[#3]

Thanks Shiva.

The result i am looking for is

MTD	Full Month PY	PY Mnth To Date	Full Month Projn
£7.2	£8.2	£5.9	£9.8

To get this I am using Aggr function and it is not working with the if condition where; for a particular agency if it does not have any business last year (column PY MTD) the full month projn should be equal to MTD column and then want the summary of all row values.

It works fine if I have agency with prior year values. and comes the total as 9.2 but what I want is the values for agency ABC12349 and ABC12364 .3 each in MTD column to be considered while calculating Full Month Projn total.

[buzzy996 · Accepted solution]

=Round(If(Dimensionality() = 0,sum(if(PYMTD >0, (MTD*FullMonthPY)/PYMTD,MTD))),0.1) --use this expression for "Full Month Projn"!

[buzzy996]

if ur happy with tht,can u close this thread?