Filling aggregated values in a column based on conditions

[GaRaGe]

I have a input dataset below

Name	scheme	ratio
A	fix	5
B	fix	5
C	light	30
D	extra	--
E	extra	--

I need to create a new field "Final" which needs to calculate values based on the columns "scheme" and "ration"

Rules for "Final" column:

1. sum of [Final]=100
2. If [Scheme]="fix" OR "light", then [Final]=[ratio]
3. If [Scheme]="extra" then [Final] should equally distribute the remaining of 100 from the rule 2

In this case, my final table should look like below

Name	scheme	ratio	final
A	fix	5	5
B	fix	5	5
C	light	30	30
D	extra	--	30
E	extra	--	30

Any suggestions on how to do this calculation in Alteryx? (maybe using the Formula tool)

[ravisharma · Accepted solution]

Hi @GaRaGe 

Something like below could work for your requirement. Hope this help. 

Thanks