Allocate Adjustment based on groups

[vjain17]

Hi All,  I am trying to find solution to below. In below data set, I need to make adjustment of $7000 with logic as, if in given data set there are companies other then "Technology" then equally divide adjustment of $7000 to all those companies. In case there are only Technology companies then divide $7k between IBM, if IBM is also not there then divide between Microsoft and then Apple. List of Technology companies includes only these three but non tech companies can be many.

Any help will be highly appreciated, Thank You

Company ID	Company Name	Company Type
256	IBM	Technology
256	IBM	Technology
158	Microsoft	Technology
158	Microsoft	Technology
203	Apple	Technology
856	Citibank	Banking
696	JP Morgan	Banking
784	Nike	Consumer
268	HSBC	Banking

[danilang]

Hi @vjain17 

The general approach would be something like this

1. Add a field [IsTech] that's either Y or N.

2. Count the number(N) of non-tech companies. 

   a. If its greater than zero, divide 7000 by N and assign this amount to all the non-tech companies using a join on IsTech

   b. If equal to zero, count the number of IBM in Tech

       1.if its greater than 0 assign a proportional amount to each IBM

       2.if equal to 0 assign a proportional amount to each non-IBM tech company

Dan