Check if a column is higher or lower and return value

[BradleyN1]

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:

XXX	XXX	Predicted	Actual
XXX	XXX	5	2	Less than predicted
XXX	XXX	3	3	As predicted
XXX	XXX	2	5	More 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 😀

[v-binbinyu-msft · Accepted solution]

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]

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

[devesh_gupta]

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]

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]

@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.

[BradleyN1]

Hey,

Thanks for this, it tells me 'Function 'CALCULATE' is not allowed as part of a calculated column DAX expressions on DirectQuery models'