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 first...
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.

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.

    • iamsujith's avatar
      iamsujith
      Frequent Visitor
      6 years ago

      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.