Forum Discussion

Peter_Ronaldon's avatar
Peter_Ronaldon
Frequent Visitor
2 years ago
Solved

DAX Help to Replicate Google Sheets Formula

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 wor...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

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

bhanu_gautam's avatar
bhanu_gautam
Super User
2 years ago

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