Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to write a formula to capture the following metrics in Power BI and Power Query:
Also,
I have two columns:
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.
Solved! Go to Solution.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |