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