Create a measure that returns a column's value if it exists, returns another column's value if not

[JS8885]

Hello,

I am creating a report connected to a live data source that has actual budget spent and projections for future years on a monthly basis. I can't add a calculated column since it is a live data source so the best I can do is a measure.

I am trying to create a measure to return an actual amount of budget spent if the data is there and a projection if not. I'd like it to pull the number from an "Actuals" column, and if there is nothing in that "Actuals" column for that month/year, pull the value from the "Projected" column.

In excel it reads very easily like: "=IF('Actuals'="",'Projected', 'Actuals')".

But I am having issues recreating the same sort of thing in a measure in Power BI so I can add it to a matrix that shows these fields for each separate budget:

A - Projected

B - Actual

C - Actual/Projected if no actual (This measure)

D - Delta between A and C (Probably another measure?)

Any suggestions or if this is even possible? It seems like it should be possible but I've tried a bunch of different things and none have stuck.

Thanks!

[AilleryO · Accepted solution]

So the good news is, it should be easy :

1/ Create measure for basic SUM, ie :

Tot Actual = SUM( YourTable[Actual] )

Tot Projected = SUM( YourTable[Projected] )

2/ Then make a test in a new measure, between your 2 measures :

Actual or Projected = IF( ISBLANK( [Tot Actual] ) , [Tot Projected] , [Tot Actual] )

3/ and calculate Delta :

Delta btwn Projected and Act or Proj = [Tot Projected] - [Actual or Projected]

It should work, let us know...

[JS8885]

Yes this worked, as soon as I saw your solution I immediately felt very dumb. I didn't even think of making measures that I could then create the measure I wanted from. I appreciate the help!

[AilleryO]

Don't worry, it's a quite common mistakes to forget about what I call basic measures,

but they are definitely the best first steps to make 😉

[AilleryO]

Hi,

AS far as I can understand your situation, you need to think out of Excel. In Excel you would have played with positions of cells, in DAX you'll have to play with a common information. It seems in your case the only "bridge" is the month ? or maybe not even ? Or maybe it's all in one table ?

Anyway you need to find the bridge between your data, actuals and forecast, to achieve what you need. It might as well require an iterative calculation (row by row) depending on your test ?

It's difficult to help you more without more informations but I hope this will do a little help...

[JS8885]

Sorry, I wasn't entirely sure what other info would be needed, this is all in one (massive) table. Each of Actuals and Projected are columns in this table.