How would I set commission rates depending on the employee?

[Anonymous]

Hello there, I currently have a commission calculator that when a Deal slicer is selected, it calculates the commission. Every Deal has a Profit and currently that Profit is calculating a 10% commission, but I want to change that depending on the Deal Owner (Employee). Here is the Report below:

Currently the measure used for the Calculated Commission card is this:

Commission = 'Accounts (CRM)'[Profit] / 10

Each Deal has a profit, and this measure just takes 10% off that profit and presents as the commission. However, I want to set this so it's dependant on Deal Owner. What I want to do is set different %'s based on the Deal Owner for the selected Deal. (Deal Owner and Deal Name are both from the same table which is Deals (CRM), whereas the Profit measure is from the Accounts (CRM) table).

Here are the commission rules that I want to implement: 

Employee 1 takes 10% (so profit / 10)

Employee 2 takes 20% (so profit / 20)

Employee 3 takes 30% (so profit / 30)

How would I be able to implement this into the measure? 

[parry2k]

@Anonymous as far as the logic worked, all good. Cheers!!

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@Anonymous finally I now know, try this:

Commission = 
VAR __employee = SELECTEDVALUE ( DealTable[Employee] )
VAR __rate = 
SWITCH ( __employee, 
   "Employee 1", .10,
   "Employee 2", .20,
   "Employee 3", .30,
   .10 //default rate
)
RETURN
DIVIDE ( SUM ( Table[Profit] ), __rate )

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Thank you @parry2k 

The only thing I changed was removing the Sum for Profit since Profit was a measure, but it worked a treat. Thank you for making the effort that you did. I most likely confused you when I was saying 10%, 20% and 30%, instead of divide by 10, 20, and 30, that was my fault. 

[parry2k]

@Anonymous I wish your answer is more clear to me, see below:

- rate is not stored anywhere, correct?

- rate is based on the employee using the if statement, if employee 1 then 10% if employee 2 then 20% if employee 3 then x% etc?

correct?

[Anonymous]

That is correct @parry2k 

I'm not sure how to use the IF statement in this instance. 

[parry2k]

@Anonymous thanks for explaining but it is still not clear where the value 30 is coming from:

 I want it to divide by X dependent upon the Deal Owner. So if I select a Deal and the Deal Owner is "Employee 3" then I want the measure to divide by 30 instead of 10. 

[Anonymous]

No problem @parry2k 

This is just a request so that instead of just divide by 10, I want to divide by 20 or 30 if the Deal Owner for that selected Deal is Employee 3 instead of 1, I want the Profit to be divided by 30 instead of justt the standard 10. 

So all I want to do is add more rules to the current commission measure I have now, which is simply dividing the [Profit] measure by 10 consistently. 

[parry2k]

@Anonymous well, it is not that easy to answer if you explain how your data model looks like and how tables are connected and how you want the things to get calculated. just simply providing that you want it dynamic based employee 1, 2, 3, doesn't help since the rate is not part of the employee.

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Hi @parry2k 

I understand what you're saying, but I'm currently working with many tables and a lot of irrelevant info, I don't want to confuse people while posting a lot of data. 

But I'll post the relevant tables for you to try and make it make sense. 

Accounts isn't that relevant here but I thought I'll post it as it's the centre of my star schema. However, each account (a customer not a user) can have several Deals, each Deal has a Deal Owner (User). Unfortunately, there are no commission rate columns. 

Let me go back to the Report now:

The Deal slicer is taking [Deal Name] column from the Deals (CRM) table, and depending on what Deal is selected, it will show the [Deal Owner] in another visual which for this selected Deal is "Employee 1", this is also in the Deals (CRM) table. 

Now each Deal has its own profit (which is another measure). Currently I use that measure for each Deal in another Measure called "Commission" and all that does is divide the "Profit" measure for that filtered Deal by 10. However, I want it to divide by X dependent upon the Deal Owner. So if I select a Deal and the Deal Owner is "Employee 3" then I want the measure to divide by 30 instead of 10. 

Does that make things more clear? Let me know if so, thank you for your effort to understand. 

[parry2k]

@Anonymous so what is the logic for the % rate?

[Anonymous]

Hi @parry2k 

Currently it's only dividing a measure in another table. I have a Profit measure that calculates the Profit of each Deal, that's in a table called Accounts (CRM). 

This is the commission measure that I'm using and want to modify:

Commission = 'Accounts (CRM)'[Profit] / 10

So currently it's just dividing the Profit for the Deal by 10 to get that commission figure. However, I want it to be determined by the Deal Owner instead. So if a Deal Owner is Employee 2 instead of Employee 1, I want it to divide the profit by 20 instead, so it's 20% of the profit. 

Just to clarify, each Deal has a Deal Owner column, both are displayed on the report if you look at the image on my post. The current Deal selected has Employee 1 as a Deal Owner. 

[parry2k]

@Anonymous add a measure

Commission = 
DIVIDE ( SUM ( Table[Profit] ), MAX ( EmployeeTable[CommissionRate] ) )

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Hi @parry2k thanks for the response. 

The only problem with that is that I don't have a [Commission Rate] column, so I won't be able to use that method.