How to Create Measure for Multiple Criteria in Power Query

[mdrammeh]

Hello,

I need help creating the following measures in Power Query and Power BI separately:

Measure

• If the “Cost variance” is less than or equal to -$10,000 and is -10% or Greater, return “Under Budget”; Also return “Under Budget” when the % Change is less than or equal -10%
• If the “Cost variance” is Greater than or equal to $10, 001, and is 10% or greater, return “Over Budget”
• If the “Cost variance” is between $9,999 and $10,000, return “On Target”
• Otherwise, return blank

Investment $	Budget Dollars	Cost Variance	% Change	Measure
$34,500,352	$20,176,565	$14,323,787	42%
$65,000	$3,949,110	($3,884,110)	-5976%
$34,565,352	$24,125,675	$10,439,677	30%

See attached picture with data sample

[Sean]

@mdrammeh

Column = 
SWITCH (
    TRUE (),
    'Table'[Cost Variance] <= -10000 && 'Table'[% Change] <= -.1, "Under Budget",
    'Table'[Cost Variance] >= 10001 && 'Table'[% Change] >= .1, "Over Budget",
    'Table'[Cost Variance] >= 9999 && 'Table'[Cost Variance] <=1000, "On Target",
    BLANK ()
)

Measure = 
SWITCH (
    TRUE (),
    MIN ( 'Table'[Cost Variance] ) <= -10000 && MIN ( 'Table'[% Change] ) <= -.1, "Under Budget",
    MIN ( 'Table'[Cost Variance] ) >= 10001 && MIN ( 'Table'[% Change] ) >= .1, "Over Budget",
    MIN ( 'Table'[Cost Variance] ) >= 9999 && MIN ( 'Table'[Cost Variance] ) <=1000, "On Target",
    BLANK ()
)

Hope this helps.

Good Luck!

[mdrammeh]

Thanks for the quick response. I just want to make sure am doing this correctly because it's showing an error message

[Sean]

The above was DAX

In the Query Editor the formula would be...

= if [Cost Variance] <= -10000 and [#"% Change"] <= -.1 then "Under Budget"

else if [Cost Variance] >= 10001 and [#"% Change"] >= .1 then "Over Budget"

else if [Cost Variance] >= 9999 and [Cost Variance] <= 10000 then "On Target"

else null

[mdrammeh]

It worked everywhere except where the % Change field = "null". How can I correct this error?

[dchager]

You have to filter out the null values.

[geoffwood]

I'm not sure it's related to your error, but shouldn't the On Target statement cover the range from minus9999 to 10,000?

[Datatouille]

Hi @mdrammeh

Just to make sure I understand well your requirement. Do you want to create a measure or a calculated column ?

See here the difference and which option better fits your requirement: http://exceleratorbi.com.au/calculated-columns-vs-measures-dax/