Create a formula in a new column

[Anonymous]

Hi everyone,

I am trying to create the following formula in power BI:

IF (demand > forecast, ABS ((forecast- demand)/forecast*100), ((demand-forecast)/demand*100)).

This is what I wrote: 

if([demand]>[forecast], ABS(([forecast]-[demand])/[forecast]*100), (([demand-[forecast])/[demand]*100)

But it is giving me the following error: "Token RightParen expected"

I am quite inexperienced in powerbi, can someone help me write the formula correctly?

Thank you very much in advance!!

Best regards.

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a calculated column as below.

Column = 
IF(
    [Demand]>[Forecast],
    ABS(
        DIVIDE(
            [Forecast]-[Demand],
            [Forecast],
            0
        )*100
    ),
    DIVIDE(
        ([Demand]-[Forecast]),
        [Demand],
        0
    )*100
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans · Accepted solution]

Hi @Anonymous 

That looks like a Power Query error. Power Query if/then/else logic is exactly like Excel IF() function logic, but the syntax is very different. Here is how you should write it:

=if [demand]>[forecast] 
then Number.Abs(([forecast]-[demand])/[forecast]*100)
else (([demand]-[forecast])/[demand]*100)

A few things:

1. Power Query is case sensitive. [Demand] is not the same as [demand] for instance.
2. All PQ functions start with the type of function it is, so ABS in Excel is Number.Abs in Power Query. Same with Number.Round, etc. They are also CamelCase, so Number.Round, Number.Abs, Text.BetweenDelimiters, Text.Trim, etc. Number.ABS won't work for example.
3. in PQ, if/then/else are always all lowercase. IF/THEN/ELSE or If/Then/Else will not work.
4. In PQ, it must be if/then/else. You cannot leave off the else like you can in Excel or DAX. If you don't need an else, then you finish with else null.
5. You can put it all on one line. I like breaking it out to make it easier to read though. Personal Preferences.

If you are doing a calculated column (not recommended per links below) then the solution @AlB would work as it is very excel-like in its structure, but in general, that table view in Power BI is not at all like an Excel grid of data or Excel table, and if you treat it as such, you'll start slowing your model down and run into issues. So, if you have put it in Power Query, as I think you have based on the error, that is the way to go. Just learn a slightly different if/then/else construct vs the old IF() statement in Excel. 👍

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
SQLBI Video on Measures vs Calculated Columns
Creating a Dynamic Date Table in Power Query

[AlB]

@Anonymous 

If you want to do this in DAX (although the error message seems to come from PQ) you're missing a closing ] and a final")"

IF (
    [demand] > [forecast],
    ABS ( ( [forecast] - [demand] ) / [forecast] * 100 ),
     ( ( [demand] - [forecast] ) / [demand] * 100 )
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers