Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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:
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi, @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.
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:
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
41 | |
29 | |
23 | |
21 | |
17 |