Announcements
cancel
Showing results for
Did you mean:
Not applicable

## How to multiply a subtotal for a value in each row

Hi everybody.

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

Product NameProduct ComponentComponent %Product q.tyComponents Q.ty
Product 1Component 130%105
Component 220%70
Component 350%175
350
Product 2Component 480%160
Component 520%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!

1 Solution

Accepted Solutions
Partner - Champion III

It could be done like this:

10 Replies
Partner - Champion III

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:

Not applicable
Author

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

Partner - Champion III

It could be done like this:

Not applicable
Author

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

Partner - Champion III

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:

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

Not applicable
Author

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!!

Partner - Champion III

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

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

Not applicable
Author

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?

Partner - Champion III

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

Community Browser