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
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors