Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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.
@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 .....
@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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |