a measure to show the rebate required bases on tiered monthly revenue

[Kieran_choudary]

hi guys,

im trying to create a measure that will show the rebate required to a customer based on the policy below

Monthly revenue, $		discount
From	To	Increment		rate
$0	$1,000	$1,000
1,001	2,000	999
2,001	3,000	999		1.0%
3,001	4,000	999		1.5%
4,001	5,000	999		2.0%
5,001	7,000	999		2.5%
5,001	12,500	4,999		4.0%
12,501	17,500	4,999		5.0%
17,501	22,500	4,999		6.0%

Im sure its pretty simple but im new this, id like to be about to show this in a table if possible against each account we have.

Look foward to seeing your solutions!

[TomMartens]

Hey, it seems that the last lines of your sample data are a little flawed regarding the increment

[vanessafvg]

@Kieran_choudary best thing to do is use a switch statement if i understand you correctly

ie.

rebate = switch(true(),

and (monthlyrevenue >= 0, monthlyrevenue <= 1000), 1,

and (monthlyrevenue > 1000, monthlyrevenue <= 2000), 1.5,

etc

)

[Kieran_choudary]

ok im defiantly getting closer!

any idea why some give me the value i need and some say true/ false, i need the all as numbers to create the next measue

[Interkoubess · Accepted solution]

Hi @Kieran_choudary,

You need to specify which rebate do you have when the revenue is less than 2000"

I don't find it in your formula and you can add another value ( zero or blank) for values not contain in all these intervals.

Thx

[Kieran_choudary]

Thank you!! you just saved me 5 hours a week!!

[Kieran_choudary]

thanks for you help!!

But this is what it says

[vanessafvg]

@Kieran_choudary

you have too many brackets

and ( condition1, condition2) 

not

and (condition1,(condition2)