Forum Discussion

mhammo_aka's avatar
mhammo_aka
Frequent Visitor
7 years ago
Solved

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

 

 

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
Need Help
  • mhammo_aka's avatar
    mhammo_aka
    7 years ago

    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

3 Replies

  • Nolock's avatar
    Nolock
    Resident Rockstar
    7 years ago

    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's avatar
      mhammo_aka
      Frequent Visitor
      7 years ago

      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's avatar
        v-chuncz-msft
        Community Support
        7 years ago

        mhammo_aka ,

         

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