How to Calculate Measure using IF Function

[KatieH]

Hello, I'm hoping for some help on making a measure using an IF statement.

I have a data set of quotes which will be made against 4 different products. Each product has a different retention percentage. I want to calculate the retention rates based on the product e.g:

If Product Type = "Product A", Cash Price * 0.25

If Product Type = "Product B", Cash Price * 0.5

If Product Type = "Product C", Cash Price * 0.75

If Product Type = "Product D", Cash Price * 0.8

When I try to create an If statement as a measure, it won't let me use any of the columns of my table.

Can anyone help?

[amitchandak · Accepted solution]

@KatieH , Try a new column

Switch(True(),

MAX( [Product Type] ) = "Product A", SUM( [Cash Price] ) * 0.25,

MAX( [Product Type] ) = "Product B", SUM( [Cash Price] ) * 0.5,

MAX( [Product Type] ) = "Product C", SUM( [Cash Price] ) * 0.75,
MAX( [Product Type] ) = "Product D", SUM( [Cash Price] ) * 0.8
)