cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate with multiple tables

I have 2 Price Lists for 2015 and 2016.

 2015 Price 2016 Price Product Price Product Price Pen 5 Pen 6 Note 10 Note 10 Mouse 100 Keyboard 150

I want to know the price difference for the products included in both 2015 and 2016.

 Product 2015 Price 2016 Price Price Up Pen 5 6 1 Note 10 10 0

Can anyone tell me how to solve this with Power BI?

So far, I've created a relationship between the 2 tables, and created a metric report.

But I don't know how to add a new column for Price Up, to calculate 2016 Price - 2015 Price.

 Product 2015 Price 2016 Price Pen 5 6 Note 10 10

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Regular Visitor

## Re: Calculate with multiple tables

Hi @Jinji

Go to Edit Queries

Click on the table in which you want to add new column.

Click on "Add column" on Tool bar

Add custom column with name 'Price UP' and write the logic as  (2016 Price - 2015 Price)

Go to File > Apply & Close

Established Member

## Re: Calculate with multiple tables

@Jinji - had a quick play with it as the totals werent adding up. You can do a merge of the tables using a full outer join through query editor. Then add this conditional column

Then I renamed some columns and deleted some and added a custom column:

This then calculates the total difference properl

4 REPLIES 4
Highlighted
Regular Visitor

## Re: Calculate with multiple tables

Hi @Jinji

Go to Edit Queries

Click on the table in which you want to add new column.

Click on "Add column" on Tool bar

Add custom column with name 'Price UP' and write the logic as  (2016 Price - 2015 Price)

Go to File > Apply & Close

Established Member

## Re: Calculate with multiple tables

@Jinji - In this situation you could just use a measure to work out the difference.

First I created two measures:

Price total 2016 = SUM('Price 2016'[Price])

Price total 2015 = SUM('Price 2015'[Price])

Then I created this measure:

Price differnce 2015-16 = IF(OR(ISBLANK('Price 2015'[Price total 2015]),ISBLANK('Price 2016'[Price total 2016])),BLANK(),'Price 2016'[Price total 2016]-'Price 2015'[Price total 2015])

This last measure removes the items where there may have been no price in 2015 or 2016.

Let me know if this works.

Giles

Established Member

## Re: Calculate with multiple tables

@Jinji - had a quick play with it as the totals werent adding up. You can do a merge of the tables using a full outer join through query editor. Then add this conditional column

Then I renamed some columns and deleted some and added a custom column:

This then calculates the total difference properl

Super Contributor

## Re: Calculate with multiple tables

Hey Jinji,

1. Create new measure from DAX

Different 2015-2016 =

if( isblank(sum('2015'[Price])) || sum('2015'[Price]) = 0,

Sum('2016'[Price]),

Sum('2016'[Price])-Sum('2015'[Price]))

2. From Power Query

In Query Editor window Merge these two Tables,

Then Add new column . there u can do the Calculation eg.(2016[price) - 2015(Price))

Before that  please replace the "NULL" with 0 .    in Price field.

Let check it .....

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

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

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 287 members 3,183 guests
Recent signins: