Nested If

[balmin2304]

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

[v-jianboli-msft]

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.

[balmin2304]

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

[SpartaBI]

@balmin2304 not sure I understand. You can PM me to do a quick zoom if you want