Forum Discussion

mdrammeh's avatar
mdrammeh
Helper III
9 years ago

How to Create Measure for Multiple Criteria in Power Query

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 DollarsCost Variance% ChangeMeasure
$34,500,352$20,176,565$14,323,78742% 
$65,000$3,949,110($3,884,110)-5976% 
$34,565,352$24,125,675$10,439,67730% 

See attached picture with data sample

Tutorial Requests

7 Replies

  • Sean's avatar
    Sean
    Community Champion
    9 years ago

    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! :smileyhappy:

    • mdrammeh's avatar
      mdrammeh
      Helper III
      9 years ago

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

       

      • Sean's avatar
        Sean
        Community Champion
        9 years ago

        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