Forum Discussion
circular dependency - need help on formula
Hi P0ach3r1
Can we maybe first try to simplify the formulas? I do not see the tables but I think the formula could also be written like this.I assume that you crete the calcualted column in the same table where you also store all the columns you refer to, right?
Please build first this column
Abs_Variance = ABS ( IFERROR ( DIVIDE ( [Actual/Projected] - [Client Forecast], [Actual/Projected] ), 0 ) )and then this
RAG = IF ( [Abs_Variance] < 0.05, "Green", IF ( [Abs_Variance] < 0.01, "Amber", "Red" ) )Please tell me what happens.
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
Resident RockstarHi P0ach3r1
Can we maybe first try to simplify the formulas? I do not see the tables but I think the formula could also be written like this.
I assume that you crete the calcualted column in the same table where you also store all the columns you refer to, right?
Please build first this column
Abs_Variance =
ABS (
IFERROR (
DIVIDE (
[Actual/Projected] - [Client Forecast],
[Actual/Projected]
),
0
)
)
and then this
RAG =
IF (
[Abs_Variance] < 0.05,
"Green",
IF (
[Abs_Variance] < 0.01,
"Amber",
"Red"
)
)
Please tell me what happens.
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
grantsambornSolution Sage
Wouldn't using DIVIDE's 3rd parameter accomplish the same thing?
Abs_Variance = ABS ( DIVIDE ( [Actual/Projected] - [Client Forecast], [Actual/Projected], 0 ), )- Mikelytics
the same like what? I only rewrited your formula. Could you please show the results and say whether it goes in the right direction? 🙂
- grantsamborn
The 3rd argument safely handles division by zero. Is there some other type of error that you are trying to handle?
(I'm only asking this to confirm whether or not what I've learned is correct.)
Hi Mikelytics - Thanks for the Help - just tried it and looks like worked, I will test the data to confirm fully.
So - I'm an excel user and very new to PowerBI - hence the excel type formula.
Moving forward - I don't need to use the table name in my formula if all fields I'm using are within the formula are in the current table?
And your solution worked because we could amend the abs_variance formula to remove the calculate command?
- Mikelytics
Hi P0ach3r1 ,
Awesome and thank you for your feedback! Happy to support you on your Power BI journey! 🙂
To your question. Exactly, when you create a calculated column in a table and refer to the same table then you do not need to provide the table name. Only when you refer to other tables. But refering to other tables often you would need additonal formulas like RELATED() to get useful results.
BUT please be aware that in general calculated columns should not be your first choice when doing calculations in Power BI, there are several reasons starting looking on storage, performance and flexbility. If you need an additional column very often it is better to calculate the column in Power Query as a custom column upfront. On the other side start early to work with Measures which are more flexible.
Typically (with some exception) you are better wih Measures or Power QUery Custom Columns. Most of the advanced techniques and recommenations from long experienced users do not include Calculated Columns. SO my advide to you is to invest more time as early as possible into Measures and Power Query.
Hope this helps you a little bit on your journey. 🙂
Best regards
Michael
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
