Create column based on table

[PBINewbie920]

Hi Everyone,

I created a measure to calculate the % Variance between Actuals and Budget. Based on this, I would like to create a new column to give points based on how much variance, as show below. I tried using an "If" statement but was unable to get it to work properly. Any tips? Thank you!

% Variance	Points
less than 1%	0
1-10%	5
11-20%	10
21%+	15

[v-cgao-msft · Accepted solution]

Hi @PBINewbie920 ,

What about trying the SWITCH() function? Please try this measure.

Measure = 
SWITCH(
    TRUE(),
    [% Variance] <= 0.01 , 0 ,
    [% Variance] > 0.01 && [% Variance] <= 0.1 , 5 ,
    [% Variance] > 0.1 && [% Variance] <= 0.2 , 10 ,
    [% Variance] > 0.2 , 15
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[pranit828]

Hi @PBINewbie920 

Can you post sample data and expected output along with the "If" statement measure.

Alternatively you should multiply your value by 100 and use Switch for the range.