Create a flow to calculate incentive pay

[TJCappy]

Hello All,

I have a situation that I am struggling with and am hoping that someone will be able to help me out.

So we have some sales employees that receive incentive pay,  these incentives are tiered and change every 3-6 months.

I will exemplify what I mean and then ask my question.

Incentive for sales campaign A (running from January - March)

Sales up to 10000 receive a $500 incentive.

Sales from 10000.01 to 50000 receive an incentive of $1000.

Sales over 50000 receive $1250

Incentive for sales campaign B (running from April - June)

Sales up to 15000 receive a $750 incentive.

Sales from 15000.01 to 40000 receive an incentive of $1000.

Sales from 40000.01 to 60000 receive an incentive of $1250

Sales over 60000 receive $1750

Please be aware that the incentives and tiers fluctuate, and no two incentive plans are the same, sometimes only the tiers change, other times it's the incentive that changes and other times it is both.

To track and validate these incentives we use a Power Apps Canvas App that inputs the data into a SharePoint list.

and uses a calculated column to record the appropriate incentive.  

This is the formula used in the calculated column.

=IF(AND([Actual sale amount (For back office use only)]>=1000,[Actual transfer amount (For back office use only)]<10000),500,IF(AND([Actual sale amount (For back office use only)]>=10000,[Actual sale amount (For back office use only)]<60000),750,IF(AND([Actual transfer amount (For back office use only)]>=60000,[Actual sale amount (For back office use only)]<250000),1000,IF(AND([Actual transfer amount (For back office use only)]>=250000,[Actual sale amount (For back office use only)]<1000000),1500,IF([Actual sale amount (For back office use only)]>=1000000,3000,0)))))

What I have been doing till now is creating a new calculated column with the current incentive every time the program changes and then map the column to the incentive field in the Power app form.  This is, whoever, not efficient in many ways but most of all when a salesperson forgets to enter the information before the deadline (usually Sunday at midnight which allows salespeople a weekend between the current and new program).

What I would like to do, is create a flow that would look at the sale amount, and then based on the campaign date chosen from a dropdown by the employee, would apply the correct incentive and input the result into the incentive column in share point. 

I have tried several things but I am afraid I am still too new to the power product suite to have had any success.

I can't even seem to find a way to store the incentive details in a sharepoint list in way that can be used by in a flow.

If anyone has any suggestions I would be grateful.

Thanks

TJ

[VictorIvanidze]

Just hire somebody and pay.

[grantjenkins · Accepted solution]

The biggest issue here is the way you're storing your data. I would go with either Dataverse or SharePoint. In this example, I'll use SharePoint. I would have two SharePoint Lists (Incentives and Incentive Details). See below.

Incentives would contain the main details for each campaign - Title, Start Date and End Date (you could add more fields as required). You would need to ensure that when you entered the data, none of the dates overlapped.

Incentive Details would contain a row for each of the initiative cut-offs and be linked back to the Incentive it's associated with.

Below are details of each column in the list. NOTE that ALL columns are set as mandatory.

Title is a mandatory field, so I would just set a default value of 'Campaign' so you don't need to enter anything. To do this, you would need to go into the List settings, click on the Title field and set the Default value.

Incentive is a Lookup field that links to the Incentives list. I've also added the Start Date and End Date as additional column, so they show up in our list automatically (not required to add the additional fields, but nice to see in the list for a bit of context).

From and To are Currency fields as shown below.

Amount is a Currency field as shown below.

After you have setup your incentive in the Incentives list, you would add each of the details in the Incentive Details list. When adding the details, you would be presented with the following input form.

Using this setup, you could look at the date of the sale and retrieve the incentive from the Incentives list within that date range, then retrieve the item from Incentive Details list with that Incentive ID, and within the number range (From and To). This would give you the amount the person should receive as their incentive.

The other main benefit with this is that you never need to update your Power App as it will just look at the current data within the lists.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[TJCappy]

Thank you @grantjenkins your post has been most helpful.