cancel
Showing results for
Did you mean:

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

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
Helper I
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)))))
3 REPLIES 3
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...
Helper I

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

Helper I
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)))))

Announcements

#### Power BI Monthly Update - November 2023

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

#### Fabric Community News unified experience

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

#### 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