Scatter Plot - How to mimic Excel capability into PowerBI

[iamsujith]

I am attempting to emulate a staff bonus structure built in Excel on PowerBI (for real time data), but I am at a dead end and do not know how proceed further. I will explain the bonus structure firstly and then explain where I am stuck at. 

Staff Bonus Structure 
I am trying to build a staff bonus for a retail store based on two KPI's - Sales Score and Customer Feedback.

Staff	Sales Profit	Sales Score	Customer  Feedback
Alex	$9,800	98	65
Gustav	$5,400	54	79
Sergio	$6,600	66	75
Matt	$9,200	92	98
Ryan	$8,500	85	50

Sales Score (Y axis) and Customer Feedback (X Axis) are plotted on a Scatter chart, the result of staff scores are allocated to various tiers as per Scatter Chart on Excel (with text boxes to indicate tiers) 

Based on the resultant tiers, a percentage weighting is multiplied to the Sales Profit to determine the staff's bonus.
Tiers and Weighting:

Tiers	Weighting
Tier 1	8.00%
Tier 2	7.50%
Tier 3	7.00%
Tier 4	6.50%
Tier 5	6.00%
Tier 6	5.50%
Tier 7	5.00%

Excel Results:

Staff	Tier	Bonus
Alex	Tier 5	$588.00
Gustav	Tier 6	$297.00
Sergio	Tier 5	$396.00
Matt	Tier 2	$690.00
Ryan	-	$0.00

Now, I have replicated most of this bonus structure in PowerBI except computing the respective Tiers and the bonus.

• How can I obtain the resultant Tier based on the (x,y) values?

Any directions/advice would be great and means a lot to this project.

Thanks in advance.
Suj

[TomMartens]

Hey @iamsujith ,

you have to express/provide a rule how the x/y values are assigned to a coresponding tier, this then can be used to create a DAX statement to multiply the Sales Profit with the percentage.

Regards,

Tom

[iamsujith]

Hi @TomMartens,

Thanks for the quick response. 

Would you be able to walk me through an example of a rule creation to show how (x,y) values can be assigned to a tier?

All I could think of is Nested IF statements to assign each Tiers?

Is that the best way to go ahead?

[v-xicai · Accepted solution]

Hi @iamsujith ,

You may create column in table 'Staff Bonus Structure' like DAX below.

Tier=
Var x='Staff Bonus Structure'[Customer Feedback]
Var y='Staff Bonus Structure'[Sales Score]
Return
SWITCH(
TRUE(),
x>=90&& y>=100, "Tier 1",
(x>=90&& y>=80 && y<100) ||(x>=80&&x<90&& y>=100), "Tier 2",
(x>=90&& y>=60 && y<80) ||(x>=80&&x<90&& y>=80&&y<100) ||(x>=70&&x<80&& y>=100), "Tier 3",
(x>=90&& y>=50 && y<60) ||(x>=80&&x<90&& y>=60&&y<80) ||(x>=70&&x<80&& y>=80&&y<100) ||(x>=65&&x<70&& y>=100), "Tier 4",
(x>=80&&x<90&& y>=50 && y<60) ||(x>=70&&x<80&& y>=60&&y<80) ||(x>=65&&x<70&& y>=80&&y<100), "Tier 5",
(x>=70&&x<80&& y>=50 && y<60) ||(x>=65&&x<70&& y>=60&&y<80), "Tier 6",
x>=65&&x<70&& y>=50 && y<60, "Tier 7"
)

Then you can create relationship between the table 'Staff Bonus Structure' and 'Tiers and Weighting' on tier.

Finally, you can create column in table 'Staff Bonus Structure' to get Bonus like DAX below.

Bonus= 'Staff Bonus Structure'[Sales Profit] *RELATED('Tiers and Weighting'[Weighting])

Best Regards,

Amy

Community Support Team _ Amy

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

[iamsujith]

Hi Amy,

This worked like a charm. Thanks for taking the time and helping me resolve my query. It has helped me overcome a major obstacle in the project and I just cannot thank you enough.

Have a good day.