cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mhammo_aka
Frequent Visitor

Subtracting Null/Blank values between 2 columns

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

 

calculating-blank-values.png

 

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?
 
 
Mike
1 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

View solution in original post

3 REPLIES 3
Nolock
Resident Rockstar
Resident Rockstar

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.

 

Capture.PNG

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

@mhammo_aka ,

 

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors