Weighted Utilization % in Pivot based on Volume, Plant & Product Group

[vanbooth]

Hi All,

Really hoping for some help here in calculating the correct weighted utilization.

The idea is you have multiple plants within each product group and within each plant you have multiple lines with different possible volumes.
Based on the volume assigned to the line you have a utilization percentage. I need a formula which dynamically checks the product group and plant, the number of lines for each plant and then their utilization weighted by volume per month.

This is a manual example but I need to make it dynamic within a pivot.

Total Capacity (mT)	PLANT/LINE1	2,220
	PLANT/LINE2	2,515
	PLANT/LINE3	1,446
	PLANT/LINE4	828
SUM Total Capacity	PRODUCT GROUP1	7,010
Sum of Total Capacity of Line/Sum of Total Capacity All Lines	PLANT/LINE1	32%​
	PLANT/LINE2	36%​
	PLANT/LINE3	21%​
	PLANT/LINE4	12%​
Multiply by Utilization	PLANT/LINE1	16	% stored as number from data source
	PLANT/LINE2	36	% stored as number from data source
	PLANT/LINE3	14	% stored as number from data source
	PLANT/LINE4	2	% stored as number from data source
Sum of All Utilizations	PRODUCT GROUP1	68	= Weighted Utilization
			SUMIF

Note: the list of plants, lines and product groups all vary

Thanks in advance!

 

[vanbooth]

Sorry also important to add all the data items are stored in one column, also in the data source so I need to do it as a calculated item