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.
Hello everyone,
I have to report what is the achievement vs target but I have a problem when the targets or actual are negative.
There are eight scenarios as you can see below.
Is there any specific formula to sum up all this scenarios?
Thanks,
Solved! Go to Solution.
If the formula not consistent, there will be no way to program it. Can you build up a series of IF/THEN statements to describe how each formula is created?
I'm not quite sure about your rule. I create a calculated column based on your sample data (a lot of nested IFs).
Actual Vs Target = IF ( 'Table'[Actual] / 'Table'[Target] > 0, IF ( 'Table'[Actual] > 0 && 'Table'[Target] > 0, 'Table'[Actual] / 'Table'[Target], IF ( 'Table'[Actual] > 'Table'[Target], 'Table'[Actual] / 'Table'[Target] + 1, - ( 'Table'[Actual] / 'Table'[Target] ) ) ), IF ( 'Table'[Actual] < 'Table'[Target], 'Table'[Actual] / 'Table'[Target] - 1, 1 - 'Table'[Actual] / 'Table'[Target] ) )
Regards,
What is your Excel calcuation for "Real Actual vs Target" ?
Please find bellow
I'm not quite sure about your rule. I create a calculated column based on your sample data (a lot of nested IFs).
Actual Vs Target = IF ( 'Table'[Actual] / 'Table'[Target] > 0, IF ( 'Table'[Actual] > 0 && 'Table'[Target] > 0, 'Table'[Actual] / 'Table'[Target], IF ( 'Table'[Actual] > 'Table'[Target], 'Table'[Actual] / 'Table'[Target] + 1, - ( 'Table'[Actual] / 'Table'[Target] ) ) ), IF ( 'Table'[Actual] < 'Table'[Target], 'Table'[Actual] / 'Table'[Target] - 1, 1 - 'Table'[Actual] / 'Table'[Target] ) )
Regards,
Hi, thanks for the nested ifs formula,
I tried it myself and it works! but just a point on one of the formula (in red),
I had to change it to (Actual-Target)/ABS(Target) + 1.
it works for the example of Actual = -5 and Target = -10,
However, it won't work when Actual = -8 and Target = -10. your formula will give 180% instead of 120% .
Actual Vs Target = IF ( 'Table'[Actual] / 'Table'[Target] > 0, IF ( 'Table'[Actual] > 0 && 'Table'[Target] > 0, 'Table'[Actual] / 'Table'[Target], IF ( 'Table'[Actual] > 'Table'[Target], 'Table'[Actual] / 'Table'[Target] + 1, - ( 'Table'[Actual] / 'Table'[Target] ) ) ), IF ( 'Table'[Actual] < 'Table'[Target], 'Table'[Actual] / 'Table'[Target] - 1, 1 - 'Table'[Actual] / 'Table'[Target] ) )
It works, Simon!
Thanks.
If the formula not consistent, there will be no way to program it. Can you build up a series of IF/THEN statements to describe how each formula is created?
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 |
---|---|
105 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |