DAX Help to Replicate Google Sheets Formula

[Peter_Ronaldon]

Hi All!

I am new here so I appreciate any support or help with the following:

Some background:

I have a Google sheet that I was previously using to track the results various KPIs of a work department (Revenue, etc). I was using several IF statements in Google sheets to essentially calculate an employees 'success %' in a given quarter. Where, Success % is an employees ACTUAL performance for a given KPI (i.e revenue generated) compared to a pre-determined target. This would ultimately help determine what Bonus this employee would receive.

The pre-determined target mentioned above is broken up into 3 categories (50%, 100% and 150%) where if the employee reached the exact target, they would receive 100% of their bonus. If the employee reached a certain level of performance below their 100% target they may be entitled to 50% of their bonus and if they overachieved their target by a certain level they could receive 150% of their bonus for any given KPI.

My question:

I have included the Google Sheet that I had previously been using here:

https://docs.google.com/spreadsheets/d/1Rr-fA6vWbtasovVnpH-FiTk18SLon4ihxd7toF7u6MQ/edit?pli=1&gid=2...

I need to replicate the formula in the four 'Success %' columns in this spreadsheet in PowerBI (whether that be as a calculated column or as a measure?) I'm not sure about the best way to go about doing this as like I said, I am somewhat new to BI. I am happy to model the data differently if need be however, I have managed to visualise the data in a somewhat similar way that I have shown in the above spreadsheet so mainly need help with this DAX expression.

Please let me know any suggestions/help you can provide.

I appreciate it!

TIA ❤️

[bhanu_gautam · Accepted solution]

@Peter_Ronaldon , You can create a new calculated column using

Success% =
VAR Actuals = Performance[Actuals]
VAR Target50 = Performance[Target50]
VAR Target100 = Performance[Target100]
VAR Target150 = Performance[Target150]
VAR F2 = 100
VAR E2 = 50
VAR G2 = 150

RETURN
IF(
F2 + (Actuals - Target100) * (G2 - F2) / (Target150 - Target100) > 150,
150,
IF(
F2 + (Actuals - Target100) * (G2 - F2) / (Target150 - Target100) > 100,
F2 + (Actuals - Target100) * (G2 - F2) / (Target150 - Target100),
IF(
E2 + (Actuals - Target50) * (F2 - E2) / (Target100 - Target50) < 50,
0,
E2 + (Actuals - Target50) * (F2 - E2) / (Target100 - Target50)
)
)
)