Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Logic to remove and reallocate dollars

cking6178
8 - Asteroid

Looking for some help on a straightforward problem, but the logic is somewhat complex. I know how to solve it in a "one-off" scenario, but hoping to be able to generalize it for future use.

 

I'm doing a pricing study and I have the following fields: Cost Pool, Seller, Buyer, Direct Expense, Markup %, Hierarchy (alpha descriptor to identify cost center and other business info), Service Name, Service % Split (some hierarchies are mapped to multiple services).

 

There are 13 sellers, 90 buyers, and ~850 hierarchies. In certain instances a buyer may only buy certain services. Sounds easy, except our allocation method "sells" at a Cost Pool level, so I need to manually back out the allocated dollars to certain services within specific seller/buyer relationships and reallocate the dollars to keep the Cost Pool and seller/buyer relationship "whole".

 

For Example:

Seller A sells 5 services in total within Cost Pool 1. Buyer A only buys 4 of the services from Seller A in Cost Pool 1. Seller A sells $10MM annually to Buyer A and our allocation method sells all 5 services.

  1. Service 1 = $2MM
  2. Service 2 = $3MM
  3. Service 3 = $1MM
  4. Service 4 = $4MM
  5. Service 5 = $1MM

If Buyer A does not buy Service 2, I need to redistribute the $3MM across the remaining 4 services. Logically, I take Service 1 $'s/(Total Pool - Service 2) and get 28.5% and then multiply that with the $10MM pool, to get a new Service 1 of $2.85MM. Then repeat the process to identify the new, correct service$'s.

 

As mentioned above, I can solve this in a "one off scenario", but I'm hoping to build a repeatable generic formula to use for all the manual adjustments I need to make. The file is currently 68k records, so not ideal if I have to create a unique formula everytime.

 

Any help is much appreciated!

5 REPLIES 5
DavidP
17 - Castor
17 - Castor

Could you please post data for such a mock up scenario that shows you input data and desired output data.

cking6178
8 - Asteroid

Hi David,

 

Thanks for the reply. I've attached a sample data set. The first tab has the sample data, the second tab shows the results I'm looking to achieve. Assuming it's a mental block on my part on how to achieve this via Alteryx and make it generic enough to replicate across the full data set.

ChrisTX
15 - Aurora

Hi @cking6178

 

The attached workflow should meet your requirements.

 

Capture.PNG

 

Chris

cking6178
8 - Asteroid

Hi @ChrisTX

 

Appreciate that. Looks like I'm not going to make it as generic as was hoping so I can cascade through the ~68k records. Thanks for your efforts!

ChrisTX
15 - Aurora

Replace the file for "Original Data" - import your ~68k records.  Replace the file for "Service to Remove" - import your full list.  Should get you what you need.  Or am I missing something?

Labels