Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
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.
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))
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.
Sure, in this case you have to intorduce the new dimension.
Hi The Hulk,
Thanks for reply.Even I tried.Its working correctly.
Thanks once again,
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.
I understand
You're welcome.