Forum Discussion

iamsujith's avatar
iamsujith
Frequent Visitor
6 years ago
Solved

Scatter Plot - How to mimic Excel capability into PowerBI

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.

StaffSales
Profit		Sales
Score		Customer 
Feedback
Alex$9,8009865
Gustav$5,4005479
Sergio$6,6006675
Matt$9,2009298
Ryan$8,5008550


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:

TiersWeighting
Tier 18.00%
Tier 27.50%
Tier 37.00%
Tier 46.50%
Tier 56.00%
Tier 65.50%
Tier 75.00%


Excel Results:

StaffTierBonus
AlexTier 5$588.00
GustavTier 6$297.00
SergioTier 5$396.00
MattTier 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

 
 
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-xicai's avatar
    v-xicai
    6 years ago

    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.

4 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    6 years ago

    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's avatar
      iamsujith
      Frequent Visitor
      6 years ago

      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's avatar
        v-xicai
        Community Support
        6 years ago

        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.