Forum Discussion

BradleyN1's avatar
BradleyN1
Frequent Visitor
2 years ago
Solved

Check if a column is higher or lower and return value

Hey,

 

I have a table with numerous columns in, but two in particular I want to check whether the figures are higher or lower on each row and then return a value. The data is being pulled via DirectQuery from Dynamics.

 

One of the columns is a column which is summing the the data, and another is a measure which is counting the number of times that value appears in the data. This is an example:

XXXXXXPredicted Actual 
XXXXXX52Less than predicted
XXXXXX33As predicted
XXXXXX25More than predicted

 

I want my last column to let me know if the Actual turns out lower than Predicted.

As a bonus I have added additional values if you're able to make it work, but the less than predicted is the most important for me.

 

Thanks 😀

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi BradleyN1 ,

    Please try below steps:

    1. below is my test table in SQL Server

    2. get data from this table in Power BI Desktop with Direct Query connection type

     

    3. create a measure with below dax formula

    Measure =
VAR _pre =
    SELECTEDVALUE ( Test[Predicted] )
VAR _actual =
    SELECTEDVALUE ( Test[Actual] )
VAR _result =
    SWITCH (
        TRUE (),
        _actual < _pre, "Less than predicted",
        _actual = _pre, "As predicted",
        _actual > _pre, "More than predicted"
    )
RETURN
    _result

    4. add a table visual with field and measure

    Please refer the attached .pbix file

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • devesh_gupta's avatar
    devesh_gupta
    Icon for Impactful Individual rankImpactful Individual
    2 years ago

    Try to use the following formula for creating a measure: 

    =IF('TableName'[Actual]<'TableName'[Predicted],"Less than predicted",IF('TableName'[Actual]='TableName'[Predicted],"As predicted","More than predicted"))

     

    If you find this insightful, please provide a Kudo and accept this as a solution.

    • BradleyN1's avatar
      BradleyN1
      Frequent Visitor
      2 years ago

      Hey there, thank you.

      It does not allow me to select the Predicted field in this IF statement, its only showing measures I have created.

       

      I thought I could add a sum(predicted) but when I do that, it just makes my whole column the same value and my other columns are all duplicated.

       

      • devesh_gupta's avatar
        devesh_gupta
        Icon for Impactful Individual rankImpactful Individual
        2 years ago

        BradleyN1 Can you try to create a column instead of a measure with same formula so that if it'll allow you to use your column names inside the IF statement:

        ColumnName = IF('TableName'[Actual]<'TableName'[Predicted],"Less than predicted",IF('TableName'[Actual]='TableName'[Predicted],"As predicted","More than predicted"))

         

        Then you don't need to create another measure for sum(predicted). I'm not sure but try if it works and do let me know.

         

        If you find this insightful, please provide a Kudo and accept this as a solution.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi BradleyN1 ,

    Please try below steps:

    1. below is my test table in SQL Server

    2. get data from this table in Power BI Desktop with Direct Query connection type

     

    3. create a measure with below dax formula

    Measure =
VAR _pre =
    SELECTEDVALUE ( Test[Predicted] )
VAR _actual =
    SELECTEDVALUE ( Test[Actual] )
VAR _result =
    SWITCH (
        TRUE (),
        _actual < _pre, "Less than predicted",
        _actual = _pre, "As predicted",
        _actual > _pre, "More than predicted"
    )
RETURN
    _result

    4. add a table visual with field and measure

    Please refer the attached .pbix file

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • BradleyN1's avatar
      BradleyN1
      Frequent Visitor
      2 years ago

      Thanks, this works ğŸ˜€

      Also devesh_gupta, yours did too! I realised there was a relationship missing that was causing it to not work, thanks both