How to multiply a subtotal for a value in each row

[#1]

Hi everybody.

I've got an issue. My data appears something like in the pivot table here below:

Product Name	Product Component	Component %	Product q.ty	Components Q.ty
Product 1	Component 1	30%		105
	Component 2	20%		70
	Component 3	50%		175
			350
Product 2	Component 4	80%		160
	Component 5	20%		40
			200

I've got the "Product Quantity which is obviously related to the product and not to its components, but i need to calculate the components quantity (Numbers in GREEN) base on a simple formula: product quantity * Component %.

The fact is that i managed to multiply the TOTAL * the %, but not the subtotal (which would obviously correspond to the product quantity as other rows are = to 0.

Do you have any ideas?

Even other ways to manage it at script level or somewhere else!

Thanks everybody in advance!!

[petter]

Something like this then? I used a pivot table and turned on sub totals for Product Component on the PRESENTATION tab of the chart properties:

[#3]

Thank you Petter!

I may have not properly described the problem. But i don't have the components quantity, i just have to main product quantity which i should use somehow together with the % to calculate the components quantity.

tks again

[petter · Accepted solution]

It could be done like this:

[#5]

That is great and works!! Thanks a lot!! May I ask you another question if possible?

Is there a way to see in the same column the the result of the formula you just did for me summed together with the component quantity of other products that are made of the same components?

Let's say in your example "Product 2" is made by "Component 1" 80% and "Component 5" 20%. I would like to see in both "Component 1" rows the number: 265 (= 105 + 160)

Is it possible?

Thanks again

[petter]

You can actually have a per row pop-up (yellow tooltip) with multiple rows that show additional calculations. The pop-up will be shown by just hovering over the cells in that particular column.

Rob Wunderlich has an excellent blog post that explains how to do this:

   Google:  rob wunderlich cell popups

And you will find the link to his blog site and the article....

[#7]

Thank you again for your prompt answer! I actually needed to have the value in the table or at least the summed value in a new table.. Cause i need it for other formulas..

Anyway thanks again for your support!!

[petter]

Reading your question once more I see that it is quite easy to get what you ask for:

add an expression:

Sum( TOTAL <[Product Component]> [Components Q.ty] )

[#9]

Sounds good and simple!

Unfortunately it says there's an error in the expression which goes out if i substitute the "Q.ty in Sales Order" with a formula. The column i'm trying to calculate is "Total Sales Order Q.ty".

Any ideas?

[petter]

If [Q.ty in Sales Order] really is a FIELD it should work...