Caly
Board Regular
- Joined
- Jul 19, 2015
- Messages
- 159
- Office Version
- 365
- 2013
- 2011
- 2010
- 2007
- Platform
- Windows
Hi I have a report with data that contains orders with sales and returns. For one order there were three lines where two weee returns and one was a sale. When doing a pivot the amount of the units purchased results in a negative but the sales is positive. I can’t have the negative quantity with positive sales but is there a way to capture this with a formula?
Example below
Order 1: Quantity = -1, sales = -0.58
Order 2: Quantity = 1, sales = 35.00
Order 3: Quantity = -1, sales = -2.58
The sum total Quantity results in -1 and sales results in 31.84
But is there a way with a formula or even the pivot table to make this scenario show positive for the quantity?
Example below
Order 1: Quantity = -1, sales = -0.58
Order 2: Quantity = 1, sales = 35.00
Order 3: Quantity = -1, sales = -2.58
The sum total Quantity results in -1 and sales results in 31.84
But is there a way with a formula or even the pivot table to make this scenario show positive for the quantity?