Allocating sales across rows

Question

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!

Example data for allocating sales.xlsx

LordNeilLord · Accepted Answer

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 Answer

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.