Display Avg Value in Total

[#1]

Hi Everyone,

I have to show Each Items Avg Value in the pivot table liked shown below.

ItemNo	Quantity	Rate	Tax	ActualValue	Total Cost
I001	10	90	10	100	1000
I001	15	135	15	150	2250
I001	10	90	10	100	1000
I001	15	135	15	150	2250
Total	50	450	50	500	(6500/50) = 130

How to show this?

Thanks

[#2]

In the example I used the expression:

Actual Value: Sum(Rate + Tax)

Avg Value: Sum((Rate+Tax)*Quantity)/Sum(Quantity)

Attached file for ur reference.

[whiteline · Accepted solution]

Hi.

You can use dimensionality() function to override a calculation of Total Cost for Total row:

=if(dimensionality()=0, Sum(Sum(aggr(ActualValue*Quantity, ItemNo)))/Sum(Quantity), Sum(ActualValue*Quantity))

[#4]

Hi The Hulk,

What if I have  to show all the rows for ItemNo=I001.

Is this expression works ?

e.g. each row contains different EntryNo.

Thanks.

[#5]

Sure, in this case you have to intorduce the new dimension.

[#6]

Hi The Hulk,

Thanks for reply.Even I tried.Its working correctly.

Thanks once again,

[#7]

Hi Whilteline,

Thanks for your reply..I marked your answered as correct because..Use of Dimensionality() is important to show two different calcalation in one column.

Thanks once again.

[whiteline]

I understand

You're welcome.