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

Calculation Difference Between Two Numbers in Two Tables Only

I am trying to compare the price of two different products across time, but on some dates I might not have price date for both products, so I want to exclude dates.  As you can see on the bottom of the picture, I didn't have both prices on September 3, so when I calculate to subtract those two numbers, it assumes that's a zero instead of omitting it.  

 

Here is my current calculation:

 

GAP = 'Daily Price 1'[$]-related('Daily Price 2'[$])

 

 

Price Comparison.png

1 ACCEPTED SOLUTION

Accepted Solutions
mwegener Member
Member

Re: Calculation Difference Between Two Numbers in Two Tables Only

Hi,

 

with a if statement you can check if both prices exists.

 

GAP = IF( ISBLANK('Daily Price 1'[$]) || ISBLANK(RELATED('Daily Price 2'[$])), BLANK(), 'Daily Price 1'[$]-RELATED('Daily Price 2'[$]) )

 

The BLANK() GAP can be filtered on the visual level

2 REPLIES 2
Super User
Super User

Re: Calculation Difference Between Two Numbers in Two Tables Only

There is a visual level filter you can set to filter out zeros etc. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
mwegener Member
Member

Re: Calculation Difference Between Two Numbers in Two Tables Only

Hi,

 

with a if statement you can check if both prices exists.

 

GAP = IF( ISBLANK('Daily Price 1'[$]) || ISBLANK(RELATED('Daily Price 2'[$])), BLANK(), 'Daily Price 1'[$]-RELATED('Daily Price 2'[$]) )

 

The BLANK() GAP can be filtered on the visual level