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

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 301 members 3,110 guests
Please welcome our newest community members: