cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Luca2020
Helper I
Helper I

Management by Objectives (MBO) with Power Bi

Hi, 

I have to calculate the amount of bonuses to be distributed to the managers of the company.

The managers are evaluated by using 3 variables --> EBITDA, ROI and Appraisal. The evaluation can be 0, 1, 2 or 3 which means I have 64 possible combinations of KPI and evaluation (0-0-1 or 1-3-2, ...).

 

- If two KPI out of three have been evaluated 0 or just the EBITDA is 0, then the bonus is 0 €.

- If two KPI have been evaluated 1 or more, and the third is 0 (but the KPI with 0 cannot be the EBITDA), then the bonus is 1000 €.

- If all three KPI have been evaluated at least 1 or more, then the bonus is 2000 € (ex 1-2-2 or 1-1-1 or 1-3-2).

- if all three KPI have been evaluated at least 2 or more, then the bonus is 3000 €.

- If all three KPI have been evaluated 3, then the bonus is 4000 €.

 

Currently I've create this measure

 

Mbo  =
IF([Ebitda Evaluation] = 0, 0,
IF([Ebitda Evaluation] = 3 && [ROI Evaluation] = 3 && [Appraisal Evaluation] = 3, 4,
...
 
but it's incomplete since I require a quick rule for the remaining cases.  I'd prefer avoiding writing down all 64 combinations.
 
Can someone help me with the measure? 
Thanks!

 

1 ACCEPTED SOLUTION

That might work:
 
Mbo =
IF([Ebitda Valutazione] = 0, 0,
IF(AND([Ebitda Valutazione] >= 1, [ROI Valutazione] = 0 && [Appraisal Valutazione] = 0), 0,
IF(AND([Ebitda Valutazione] >= 1, OR([ROI Valutazione] = 0, [Appraisal Valutazione] = 0)), 1,
IF(AND([Ebitda Valutazione] > 1 && [ROI Valutazione] > 1 && [Appraisal Valutazione] > 1, NOT([Ebitda Valutazione] = 3 && [ROI Valutazione] = 3 && [Appraisal Valutazione] = 3)), 3,
IF([Ebitda Valutazione] = 3 && [ROI Valutazione] = 3 && [Appraisal Valutazione] = 3, 4, 2)))))

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@Luca2020 Use a SWITCH(TRUE()...) like:

 

Mbo = 
  SWITCH(TRUE(),
    [Ebitda Evaluation] = 0,0,
    [Ebitda Evaluation] + [ROI Evaluation] + [Appraisal Evaluation] = 9, 4,
    [Ebitda Evaluation] + [ROI Evaluation] + [Appraisal Evaluation] >= 6, 3,
    [Ebitda Evaluation] + [ROI Evaluation] + [Appraisal Evaluation] >= 3, 2,
    [Ebitda Evaluation] + [ROI Evaluation] + [Appraisal Evaluation] >= 1, 1
  )

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

The difficult part is to find the general rule because:

 

 [Ebitda Evaluation] + [ROI Evaluation] + [Appraisal Evaluation] >= 6, 3,

 

But if the evaluations are 0 - 3 - 3, the total sum is 6 but the bonus is 1.000 € and not 3.000 € because they achieved only two out of three KPI. 

 

If the evaluations are 1 - 3 - 3, the bonus is 2.000 € and not 3.000 € because at least one evaluation is below 2

That might work:
 
Mbo =
IF([Ebitda Valutazione] = 0, 0,
IF(AND([Ebitda Valutazione] >= 1, [ROI Valutazione] = 0 && [Appraisal Valutazione] = 0), 0,
IF(AND([Ebitda Valutazione] >= 1, OR([ROI Valutazione] = 0, [Appraisal Valutazione] = 0)), 1,
IF(AND([Ebitda Valutazione] > 1 && [ROI Valutazione] > 1 && [Appraisal Valutazione] > 1, NOT([Ebitda Valutazione] = 3 && [ROI Valutazione] = 3 && [Appraisal Valutazione] = 3)), 3,
IF([Ebitda Valutazione] = 3 && [ROI Valutazione] = 3 && [Appraisal Valutazione] = 3, 4, 2)))))

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors