cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fsy
Regular Visitor

IF Statement error

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

 

Screen Shot.PNG

 

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:

 

Error.PNG

 

Any suggests as to what I am doing wrong? Thank you for your help!

1 ACCEPTED 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/



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

2 REPLIES 2

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/



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

 Thank you very much, Matt!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.