Mayank, Please mock up your scenario in a workbook using the super store sales data set so we can help you. This should only take you 5-10 minutes. You can use their hierarchy of category/subcategory/product.
So let assume I want to measure profit by sale for each row. It'll look like this (see last column). But when I replace row by product id, it give me sum of all 3 percentage values [-1.18 + ( - )1.18 + 14 = 11.65%] but I ideally it should be [( -4.8392 - -4.8391 + 33.8744)/(411.332+411.332+241.96) = 12.60%]
Row ID Order ID Order Date Ship Date Ship Mode Customer ID Customer Name Segment Country City State Postal Code Region Product ID Category Sub-Category Product Name Sales Quantity Discount Profit Profit by Sale % 1761 CA-2012-130785 9/5/2012 9/9/2012 Standard Class AG-10900 Arthur Gainer Consumer United States San Diego California 92105 West FUR-BO-10000330 Furniture Bookcases Sauder Camden County Barrister Bookcase, Planked Cherry Finish 411.332 4 0.15 -4.8392 -1.18% 5495 CA-2011-105249 11/28/2011 11/28/2011 Same Day DH-13675 Duane Huffman Home Office United States San Francisco California 94122 West FUR-BO-10000330 Furniture Bookcases Sauder Camden County Barrister Bookcase, Planked Cherry Finish 411.332 4 0.15 -4.8392 -1.18% 6402 CA-2014-125472 5/31/2014 6/1/2014 First Class BD-11725 Bruce Degenhardt Consumer United States Lafayette Louisiana 70506 South FUR-BO-10000330 Furniture Bookcases Sauder Camden County Barrister Bookcase, Planked Cherry Finish 241.96 2 0 33.8744 14.00%
somehow I could not paste all the columns
I'm not getting this option to attach the file.
1 of 1 people found this helpful
I've a question if you see you've used two time minus Ideally It should be +(-). Just because of "- -" Its returning 33.8744 for Profit sum. That's why you're getting 12.60% at Product level.
I've attahced the screen shot of your post.
For your information,
2.27% at ROWID level
11.65% at Product level
Use below mentioned screen shot for your reference.
Compute using pane (down) in the copy formula. Grand Total is just for your understanding.
Modified workbook attached (version 9.3). Have a look.
Let me know If you've any query.
Kindly mark the answer as correct to close the thread logically. So that It may help others who have similar kind of queries.