Power Bi DAX: Complex Commission Plan

[paulfink]

Hi guys,

got a bit of a challenge.

So I have been given a sales commission plan for one of the managers and it is quite complex so I am not sure how I'm doing to do this so please give me a helping hand.

In the plan, there is an overall individual target given which is given per month.

Then there are additional conditions which need to be met to receive commission:

• 2.5% of all own sales total
• 5% of team order value, over monthly team target
• N1 amount ( I will put in myself ) per quarter for passing team target of N2 amount
• N3 amount per quarter for passing team count of orders of N4 amount
• N5 amount per quarter for passing order value target

N1, 3 & 5 are the amounts he will receive if the target has been reached.

N2 & 4 are targets that his team need to reach.

I will manually put in each N amount into the formula so do not think they are from their own column.

Is this clear enough?

Please let me know and I will reply back asap

[amitchandak]

@paulfink ,

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[paulfink]

added a dummy table in my post. Hope this helps

[dax]

Hi @paulfink ， 

If possible, could you please explain the logic of 5% result to me? And there the last 3 row are the fixed value? By the way, did you mean above image is your expected output? If possible, could you please inform me your data structure?

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[paulfink]

Hi @dax 

The 5% of the team order value is that if the team hit their target, then the manager receives a 5% of that sales total.

if the team target is 5000 and they make 6000 then the manager receives 30.

I have another table in my PBi report with all the targets and sales.

The table i posted is the commission plan i received. Amounts changed for privacy reasons.

Is that easier?