Forum Discussion
Nested If
Hi,
I have a table as below in Excel. However, in Power BI, how do I put a measure to calculate the rate and score based on the achieved figures. This is the if command I used in Excel to get the score. "=IF(M2<=0.65,1,IF(AND(M2>0.65,M2<0.95),2,IF(AND(M2>=0.95,M2<=1),3,IF(AND(M2>1,M2<1.15),4,IF(M2>=1.15,5)))))", where M2 is referred to as Achieved. And for each row, I changed the syntax according to the rating scale. Can I have one measure to include all these in one?
Achieved Rate Score
| 66% | 2 | 1.00 |
| 66% | 2 | 0.10 |
| 75% | 3 | 0.15 |
| 2.6 | 2 | 0.10 |
| 11% | 4 | 0.08 |
| 21% | 2 | 0.14 |
| 33% | 2 | 0.04 |
3 Replies
balmin2304 not sure I understand. You can PM me to do a quick zoom if you want
Hi balmin2304 ,
Based on your descriptions, I have changed the IF to measure:
Measure = SWITCH(TRUE(), MAX('Table'[Achieved])<=0.65,1, MAX('Table'[Achieved])>0.65&&MAX('Table'[Achieved])<0.95,2, MAX('Table'[Achieved])>=0.95&&MAX('Table'[Achieved])<=1,3, MAX('Table'[Achieved])>1&&MAX('Table'[Achieved])<1.5,4,5)Output:
The measure works fine, but the result seems to be different with the Score and Rate, could you please share more details about how to calculate them to help us clarify your scenario?
Please provide me with more details about your data and your problem or share me with your pbix file after removing sensitive data.
Refer to:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your reply and sorry for the delay in responding to you. The measure works fine, but my issue is that I have different targets for different items as shown in the table below. What I need to find is the Score based on the achieved against the rating scale mentioned for each.
Kpis KpI Wt Target Rt-1 Rt-2 Rt-3 Rt-4 Rt-5 Achieved Score Abdcd 100% As per approved budget ≤65% >65%-<95% ≥95%-≤100% >100%-<115% ≥115% 98% 3 nbnbnbn 100% 80% ≤65% >65%-<78% ≥78%-≤82% >82%-<90% ≥90% 84% 4 mnmnmn 100% 75% <60% ≥60%-<75% ≥75%-≤78% >78%-<86% ≥86% 79% 4 kjkjkjk 70% 3.5 ≤2.5 >2.5 -<3.3 ≥3.3 -≤3.7 >3.7 - <4.0 ≥4.0 3.8 4
