Subtracting Null/Blank values between 2 columns

[mhammo_aka]

Hello,

I am working on a report that flags "missing" data between 2 different data sources. It is validating that data from Source 1 made it into Source 2; and if not, flag the missing records. I have that working correctly (missing transactions are in RED). Now, I have a request to show the result of Col A - Col B. The problem I am running into is, for any "Missing" records, the difference column is showing blank values. 

In the screenshot below, I want the values in 'roar-d365 net diff' to show the difference between the 'roar net' column and the 'd365 net' column. (I typed in the values in the screenshot).

I have tried 

D365[D365 Net]-RELATED('Summary Transactions'[ROAR Net]) + 0

and

IF(D365[D365 Net]-RELATED('Summary Transactions'[ROAR Net])=BLANK(),0,D365[D365 Net]-RELATED('Summary Transactions'[ROAR Net])

Any ideas on getting this to work correctly?

Link to example file: https://www.dropbox.com/s/ye7e49ra4nusfph/calculate_blank_values.pbix?dl=0 

Mike

[Nolock]

Hi @mhammo_aka,

I've checked your formulas and they seem ok, but then I saw there is something suspicious.

I changed your formula just to ROAR-D365 Net Diff = 42 and I got the same problem. Blanks at the beginning and the value 42 later. It took me to the Relationships view of PowerBI where I saw that you have a relation between 2 tables: D365 and Summary Transactions.

It seems (at least to me) like you have 2 fact tables which you want to join. And you do that in both directions. If I change the direction from one-to-one with both directions to something else, your report stops working.

Please check your data model, if it does what you expect.

[mhammo_aka · Accepted solution]

Thank you Nolock, I figured it out finally. My calculated columns for the net difference were "backwards". I had to create the calc column in the summary table; instead of in the D365 table. 

Mike

[v-chuncz-msft]

@mhammo_aka ,

Glad to hear that. You may help accept the solution above. Your contribution is highly appreciated.