Rounding Values to Total

[thelegazy]

Hi All, 

I've gotten some great help so far, so thank you for that!

I think this time I have more of a formula question rather than what tool I'd use. 

I have a table of the following: 

Product Code	Gender	Country	Quantity - Promised	Size Code	Ratio	Quantity
Product 1	Male	Canada	125	S	.3	37.5
Product 1	Male	Canada	125	M	.5	62.5
Product 1	Male	Canada	125	L	.2	25

I want the Quantities with decimals to be rounded, but I want it to total the Quantity - Promised quantity. So, rounding up would push the total quantity for Product 1 - Gender - Country - would exceed the 125 units "Quantity - Promised". 

There's a cumbersome process right now in Excel, but I was wondering if anyone had any guidance on how to approach this. 

[JoshKushner]

How are you getting fractional quantities? Could there be a fix earlier in your flow to give you whole quantities?

[thelegazy]

Hey Josh, 

I'm using the following: 

https://community.alteryx.com/t5/Data-Preparation-Blending/Multiplying-Values-by-ratio-from-differen...

[jdunkerley79]

I suggest the following:

- Allocate a 'base quantity' equal to the Floor of the Quantity

- All will now be within 1 of the target quantity

- Compute the residual

- Filter down to just those, not at the exact quantity

- Of those you 'pick' however many you need and add 1 to them

The picking is where it gets interesting. I have used 3 methods in the past:

- Random

- Largest Percentage difference

- Largest Decimal difference.

The attached does the random allocation. If you need another approach happy to help

[thelegazy]

*Correction, I made a data entry error. Let me retry and see where I end up, sorry about that.

Hey, 

Thanks for the help. 

However, when I try to apply the solution I get duplicates in my data set. 

Also, when I try to add another product in the data set in the example provided it gives me back a number larger than the Quantity Promised for that Product - Country combination. 

I've attached the example. Thanks!!

[jdunkerley79 · Accepted solution]

I wasnt recomputing the Quantity at the start

I have added this

I have also added the other two picking methods (Largest Decimal Diff, Largest Percentage Diff) 

New version attached

[thelegazy]

Thanks for the update, I didn't get a chance to look at it till today. Thanks!