Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Preparing for a certification exam? Ask exam experts all your questions on May 15th. Register now.

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
  )

 



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

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
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.