Forum Discussion

mdrammeh's avatar
mdrammeh
Helper III
9 years ago
Solved

How to create a calculated row in a column using DAX?

I am trying to write a formula to capture the following metrics in Power BI and Power Query:

  • If the cost variance between scope and target dollars is within 10% (+/-), “Good”
  • If the cost variance between scope and target dollars is within 5% (+/-), “Very Good”
  • If the cost variance between scope and target dollars equal 0%, “Excellent”
  • If the cost variance between with less than or greater than 10% (+/-), “Needs Improvement”

Also,

  • If the cost variance between scope and target dollars is within $50K (+/-), “Good”
  • If the cost variance between scope and target dollars is within $25K (+/-), “Very Good”
  • If the cost variance between scope and target dollars equal $0, “Excellent”
  • If the cost variance between is less than or greater than $51K (+/-), “Needs Improvement”

I have two columns:

  • Scope
  • Target Dollars and
  • Variance

I also want to create a calculated field to return Maximum and Minimum Target Dollars

 

My table name is: "DE Scope Target"

 

Thank you in advance.

Need Help
Tutorial Requests
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi mdrammeh,

    For your first scenario, how to calculate the percentage? Could you please give an example based on your sample table.

    For your second scenario, you can create a calculated column to get the absolute value of the cost variance between scope and target dollars using the formula

    cost variance=ABS('DE Scope Target'[Scope] - 'DE Scope Target'[Target Dollars])


    Then get the expected result based on the calculated column as follows.

    result=IF('DE Scope Target'[cost variance]>=51,"Needs Improvement",IF('DE Scope Target'[cost variance]>=25,"Good",IF('DE Scope Target'[cost variance]>0,"Very Good","Excellent")))


    Finally, you want to get     Maximum and Minimum Target Dollars, which means the Maximum and Minimum of all Target Dollars? If it is, your [Max Target Dollars] and [Min Target Dollars] are uncorrect. You should calculate them using the formulas.
     

    Max Target Dollars=CALCULATE(MAX('DE Scope Target'[Target Dollars]),ALL('DE Scope Target'))

Min Target Dollars=CALCULATE(MIN('DE Scope Target'[Target Dollars]),ALL('DE Scope Target'))


    Best Regards,
    Angelia

2 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    Hi mdrammeh,

    For your first scenario, how to calculate the percentage? Could you please give an example based on your sample table.

    For your second scenario, you can create a calculated column to get the absolute value of the cost variance between scope and target dollars using the formula

    cost variance=ABS('DE Scope Target'[Scope] - 'DE Scope Target'[Target Dollars])


    Then get the expected result based on the calculated column as follows.

    result=IF('DE Scope Target'[cost variance]>=51,"Needs Improvement",IF('DE Scope Target'[cost variance]>=25,"Good",IF('DE Scope Target'[cost variance]>0,"Very Good","Excellent")))


    Finally, you want to get     Maximum and Minimum Target Dollars, which means the Maximum and Minimum of all Target Dollars? If it is, your [Max Target Dollars] and [Min Target Dollars] are uncorrect. You should calculate them using the formulas.
     

    Max Target Dollars=CALCULATE(MAX('DE Scope Target'[Target Dollars]),ALL('DE Scope Target'))

Min Target Dollars=CALCULATE(MIN('DE Scope Target'[Target Dollars]),ALL('DE Scope Target'))


    Best Regards,
    Angelia

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    Hi mdrammeh,

    Have you resolved your issue? If you have, please mark the helpful reply as answer. If you have not, please feel free to ask and share more details.

    Best Regards,
    Angelia