A value that there is a 10% chance of loss

[nataliad18]

Hi!

I have a column of different values, for each of those values there is a 10% *chance* of loss from that value. Is there any smarter way to calculate total, and not just value*0.9?

[DataNath]

Hey @nataliad18, are you able to give a bit more detail of what exactly you mean by 'of loss'? Is it that there's a 10% chance of losing the entire value i.e. 10% chance it = 0? Or a 10% chance it loses X%?

[nataliad18]

Yes - apologies, that was extremely unclear. 

So the value in a column is amount of customers and the value of loss is that there is a 10% chance that each of those customers will not be out customer anymore. i.e if there are 120 customers, each one of those 120 has a 10% chance of not being our customer anymore

[DataNath]

Thanks for clarifying @nataliad18. I'm sure there's probably a sophisticated/better way to handle this in a Formula, and hopefully someone else can chime in with that. However, here's something I've built out in the meantime - this does involve blowing up data so not ideal if you have several records with very large amounts of customers etc.

We start off by assigning a RecordID that we'll use later to group and Sum on:

We then generate N rows where N is the number of Customers (or whatever the field is in your true data):

Using the RandInt() function, we can generate an integer between 0 and x (9 here as we want a 10% chance). We then tell Alteryx that if this number between 0-9 is 1, assign it a value of 0, otherwise let it equal 1:

In our penultimate step, we can group by RecordID and then Sum this new randomised field we just created. As records will be assigned 0 10% of the time, they'll essentially be 'dropped' in the aggregation:

Finally, because I'm using a new version of Alteryx, AMP is the default engine and throws things out of order sometimes. Therefore, I just sorted on RecordID ascending in order to restore the original order:

As the integer in the randomising step is generated upon running the workflow, you'll get varying results each time and should be a bit more dynamic/realistic than a hard-coded *0.9 value:

Hope this helps in the meantime. Like I mentioned before, I'd imagine there's an equation that can be replicated in the Formula tool to mimic something along these lines!