Weighted average of population

[butlerrbrian]

I have a dataset that includes ZIP Codes arranged by dispatch zones as bundles inside markets that includes population figures by ZIP codes. It also contains order forecasts that are only arranged by market.

ZIP CODE MARKET Dispatch Zone Households
12345 Anywhere, TX ANY-N Anywhere 15000

12346 Anywhere, TX ANY-N Anywhere 7800

12347 Anywhere, TX ANY-N Anywhere 1250

12348 Anywhere, TX ANY-S Anywhere 1800

12349 Anywhere, TX ANY-S Anywhere 2200

MARKET MONTH Orders
Anywhere January 35

I need to apportion the 35 orders into the disptach zones based on weighted population. I am building on the idea of:

=ROUNDUP(SUMX(ZIPS, ZIPS[Households] * Orders[Orders] / ZIPS[Households]))

This is not returning the correct values.

Any thoughts are welcome - perhaps =SUMPRODUCT is where I should be?

 

[miguel.escobar]

sounds like an interesting case!

The main issue is that you're trying to use a DIM table (ZIPS) rather than your FACT table when using the X-MEN function (SUMX) which should return a really weird error or a really weird result.

Any way that you can upload a workbook with some dummy data and the way that it should end up? (as desired output - even with the #'s)