AGGR in Pivot Table

[danielle_v]

Hello,

I currently have a pivot table like the below, which shows the allocated/contracted volume % splits at country/destination/carrier level vs. the actual shipped volume and percentages.  COUNTRY, DESTINATION, CARRIER & ALLOCATION are dimensions. Shipped Volume, Actual % & Planned Volume are expressions. The expression for Planned Volume is as follows;

(sum({$<
AllocationShipment_Week = {$(vAllocationWeek)}>}
total <COUNTRY, DESTINATION>
ShippedVolume) )* [ALLOCATION]

COUNTRY	DESTINATION	CARRIER	ALLOCATION	Shipped Volume	Actual %	Planned Volume
China	F	Carrier A	25%	2	18%	2.75
China	F	Carrier B	25%	0	0%	2.75
China	F	Carrier C	50%	9	82%	5.5
China	T	Carrier A	25%	9	34%	6.6875
China	T	Carrier B	25%	0	0%	6.6875
China	T	Carrier C	50%	17.75	66%	13.375
India	F	Carrier A	30%	31	33%	28.125
India	F	Carrier B	30%	29.25	31%	28.125
India	F	Carrier C	40%	33.5	36%	37.5
India	T	Carrier A	30%	0	0%	2.55
India	T	Carrier B	30%	2.25	26%	2.55
India	T	Carrier C	40%	6.25	74%	3.4
USA	F	Carrier A	50%	16.75	100%	8.375
USA	F	Carrier D	50%	0	0%	8.375
USA	T	Carrier A	50%	2	100%	2
USA	T	Carrier D	50%	0	0%	0
Australia	F	Carrier A	40%	25.5	37%	27.3
Australia	F	Carrier B	30%	6.75	10%	20.475
Australia	F	Carrier C	30%	36	53%	20.475
			Total	227		227

This works absolutely fine, but I also need an additional pivot table like the below - so I somehow need to calculate the Planned Volume at COUNTRY, DESTINATION & CARRIER level, but only display it at CARRIER level.

Can this be done with aggr? I have tried numerous variations but I'm going round in circles and not getting anywhere!

CARRIER	Shipped Volume	Actual %	Planned Volume
Carrier A	86.25	38%	77.7875
Carrier B	38.25	17%	60.5875
Carrier C	102.5	45%	80.25
Carrier D	0	0%	8.375
Total	227		227

Any help greatly appreciated!!

[sunny_talwar · Accepted solution]

How about this?

Sum(Aggr(
  (Sum({$<AllocationShipment_Week = {$(vAllocationWeek)}>}total <COUNTRY, DESTINATION> ShippedVolume))* [ALLOCATION]
, COUNTRY, DESTINATION, CARRIER))

[danielle_v]

Thank you so much - that works perfectly!