Hi,
I’m trying to create a chart that compares Actual Revenues and Expenses to Budgeted Revenues and Expenses. I’m using Variance to Budget % = [Actual]-[Budget)/abs[Actual].
If Actual Revenue is greater than Budgeted Revenue, I want to show a positive variance. However, if Actual Expenses is greater than Budgeted Expenses I need to show a negative variance, so I need to reverse the signs of my formula depending on whether or not I’m calculating a Revenue or Expense variance %.
I have a column [Category] that classifies whether or not the amount is a Revenue or Expense. I’m trying to solve for this by using the following IF statement:
Variance to Budget % = IF(or([Actual]=0,[Budget]=0),
BLANK(),
If ([Category] = “Total Revenue”, ([Actual]-[Budget])/abs([Actual])),
, ([Actual]-[Budget])/abs([Actual])),*-1)
I get the following error:
Any suggests as to what I am doing wrong? Thank you for your help!
Solved! Go to Solution.
Replace [category] with selectedvalue(tablename[category]). but the total won't work here. I am not sure how you are going about this, but you may benefit from this video https://www.youtube.com/watch?v=ojHZkWkEY7Q/
As an aside, you should always add the table name before the column name (although this is not the issue causing the problem). https://exceleratorbi.com.au/best-practices-power-pivot-power-query-power-bi/
Replace [category] with selectedvalue(tablename[category]). but the total won't work here. I am not sure how you are going about this, but you may benefit from this video https://www.youtube.com/watch?v=ojHZkWkEY7Q/
As an aside, you should always add the table name before the column name (although this is not the issue causing the problem). https://exceleratorbi.com.au/best-practices-power-pivot-power-query-power-bi/
Thank you very much, Matt!
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
Join the biggest FREE Business Applications Event in LATAM this February.
User | Count |
---|---|
215 | |
48 | |
44 | |
44 | |
41 |
User | Count |
---|---|
271 | |
211 | |
103 | |
75 | |
64 |