Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there, I have a measure here called "Commission", and this measure determines a profit % depending on the employee's name. However, I want to be able to make it more advanced, rather than it look for an employees name, I want it to also check the current sales item.
Here is the current measure's code below:
Commission =
VAR __employee = SELECTEDVALUE ( 'Employee' [Employee Name] )
VAR __rate =
SWITCH ( __employee,
"Employee 1", 10,
"Employee 2", 20,
"Employee 3", 30,
10 //default rate
)
RETURN
'Sales '[Profit] / 100 * __rate
This measure currently works perfectly, it will scan an employee name then determine what % that employee is getting. However, I want to also scan the sales item.
Here is an example of what I'm aiming for below:
Commission =
VAR __employee = SELECTEDVALUE ( 'Employee' [Employee Name] )
VAR __saleitem = SELECTEDVALUE ( 'Items' [Item Type] )
VAR __rate =
SWITCH ( __employee,
"Employee 1", 10, SWITCH ( __saleitem,
"Hardware", 15, "Software", 20),
"Employee 2", 20,
"Employee 3", 30,
10 //default rate
)
RETURN
'Sales '[Profit] / 100 * __rate
This example above produces no errors, however, the visual cannot be displayed (card visual) so it's not working for whatever reason.
Is there a way to implement this? Or perhaps you guys can offer other suggestions?
Solved! Go to Solution.
Hey @Anonymous ,
you didn't remove the 10 for the first employee. So the case and result statements are mixed up. Try it like this:
Commission =
VAR __employee = SELECTEDVALUE( 'Employee'[Employee Name] )
VAR __saleitem = SELECTEDVALUE( 'Items'[Item Type] )
VAR __rate =
SWITCH(
__employee,
"Employee 1",
SWITCH(
__saleitem,
"Hardware", 15,
"Software", 20
),
"Employee 2", 20,
"Employee 3", 30,
10 //default rate
)
RETURN
'Sales '[Profit] / 100 * __rate
As your second switch is not really depending on the output of the first switch, you could also put it as an independent block of code. This would make it a little bit more readable:
Commission =
VAR __employee = SELECTEDVALUE( 'Employee'[Employee Name] )
VAR __saleitem = SELECTEDVALUE( 'Items'[Item Type] )
VAR __switchSaleitem =
SWITCH(
__saleitem,
"Hardware", 15,
"Software", 20
)
VAR __rate =
SWITCH(
__employee,
"Employee 1", __switchSaleitem,
"Employee 2", 20,
"Employee 3", 30,
10 //default rate
)
RETURN
'Sales '[Profit] / 100 * __rate
Hey @Anonymous ,
you didn't remove the 10 for the first employee. So the case and result statements are mixed up. Try it like this:
Commission =
VAR __employee = SELECTEDVALUE( 'Employee'[Employee Name] )
VAR __saleitem = SELECTEDVALUE( 'Items'[Item Type] )
VAR __rate =
SWITCH(
__employee,
"Employee 1",
SWITCH(
__saleitem,
"Hardware", 15,
"Software", 20
),
"Employee 2", 20,
"Employee 3", 30,
10 //default rate
)
RETURN
'Sales '[Profit] / 100 * __rate
As your second switch is not really depending on the output of the first switch, you could also put it as an independent block of code. This would make it a little bit more readable:
Commission =
VAR __employee = SELECTEDVALUE( 'Employee'[Employee Name] )
VAR __saleitem = SELECTEDVALUE( 'Items'[Item Type] )
VAR __switchSaleitem =
SWITCH(
__saleitem,
"Hardware", 15,
"Software", 20
)
VAR __rate =
SWITCH(
__employee,
"Employee 1", __switchSaleitem,
"Employee 2", 20,
"Employee 3", 30,
10 //default rate
)
RETURN
'Sales '[Profit] / 100 * __rate
Hi @selimovd thanks for your help.
However, I did prefer your 2nd option as it's better coding but for some reason it didn't work. it displayed a visual error on the card and I'm not sure why?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |