Allocating sales across rows

[MRF]

Hi,

I'm trying to allocate sales across multiple rows and am wondering the best method - multi-row tool or something else perhaps.

I have Region, Store Number, Product, Dollar Sales, and Store Location of product. The sales data doesn't/can't separate out sales for the different locations in the store (aisle, end cap, display, check out) so each record for that region/store/product/location has the same dollar amount. Not all products are in the same number of locations in each store, so there isn't a consistent number of rows per store per product - it varies from 1 to 4 rows. 

What I want to do is allocate that total amount evenly across the total number of locations in the store, otherwise, the sales amount gets double/triple/quadruple-counted and overstates sales. So, if the product is in 3 locations in the store, divide the total dollar sales by 3 and populate a new field for each of the 3 rows with that more accurate figure.

I've attached some sample sales data - I added a field "Allocated Sales" in the sample data for illustrative purposes.

Thanks in advance!

[LordNeilLord · Accepted solution]

Hi @MRF

This is a perfect job for the summarize tool!

If you take the AVG Sales (or min/max) for each region, store & product and count the number of aisles you can then calculate to allocated sales:

Then join this back into you original workflow:

I have attached an example

[rahul1011 · Accepted solution]

You can use the summarize tool to find the count of the number of times each Region, Store and product combination is appearing, append the count column to the data and use formula tool to divide by the count.

[MRF]

Thanks to you both for the quick response!

This will work very effectively.