Reply
Frequent Visitor
Posts: 6
Registered: ‎05-29-2016
Accepted Solution

Calculate with multiple tables

I have 2 Price Lists for 2015 and 2016.

 

2015 Price 2016 Price
ProductPrice ProductPrice
Pen5 Pen6
Note10 Note10
Mouse100 Keyboard150

 

 

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

Product2015 Price2016 PricePrice Up
Pen561
Note10100

 

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.

Product2015 Price2016 Price
Pen56
Note1010

 

 

 


Accepted Solutions
Highlighted
Regular Visitor
Posts: 43
Registered: ‎04-27-2016

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

 

Thus your changes are saved.

 

 

View solution in original post

Established Member
Posts: 178
Registered: ‎10-18-2015

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

 

Conditional column.PNG

 

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

 

custom column.PNG

 

This then calculates the total difference properl

View solution in original post


All Replies
Highlighted
Regular Visitor
Posts: 43
Registered: ‎04-27-2016

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

 

Thus your changes are saved.

 

 

Established Member
Posts: 178
Registered: ‎10-18-2015

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.

 

Power bi table help.PNG

 

Let me know if this works.

 

Giles

Established Member
Posts: 178
Registered: ‎10-18-2015

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

 

Conditional column.PNG

 

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

 

custom column.PNG

 

This then calculates the total difference properl

Super Contributor
Posts: 733
Registered: ‎06-03-2016

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