Budget variance revenues vs. expenses

DAX DAX Calculations
1

I’m working on a simple P&L statement and I’ve run into a problem I can’t figure out. I have 2 fact tables (actuals, budgets) that do not have a direct relationship to each other, but each is connect to several ‘filter’ tables. The problem is my variance calculation - I want to show ‘good’ variances as positive number and ‘bad’ variances as negative. Currently, I’m subtracting Actual-Budget for all categories and what I need is to subtract Budget-Actual for all the various expense categories. I’m not sure if I should be looking at an IF, SWITCH or CASE statement and I can’t figure out how I can change a calculation on the fly based on the value of single column in a filter table. Thanks in advance for any thoughts or advice.

variance%20question

2

Sure, shouldn’t be too difficult on this one.

What is your current formula?

A simple SWITCH should do it I would say.

Has you gone through SWITCH True Logic ?

What it look like you may need to do is have an index which suggests where you want to run Actuals-Budgets or Budgets - Actuals

So the table where ‘Account Category’ should have this.

Do you have a column that says ‘Expense’ and ‘Revenue’

Within this member only session here I ran through a similar example. (around the 34min mark onwards)

Also download the resources and you’ll see how I did it in this particular one.

Chrs

3

Hi Sam and thanks for your reply. The switch/true advice was spot on - I never thought of using that! Great stuff.

Bryce