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 two tables. One is Product table has two columns., Product and Price.
Product table
Product | Price |
A | 6.5 |
A | 4.2 |
B | 75 |
C | 28 |
B | 43 |
C | 62 |
A | 2.2 |
The another table has Price range for each product (categorised by Range1, Range2, Rabge3 and Range4)
PriceRangeTable
Product | Range1 | Range2 | Range3 | Range4 |
A | <=3 | >3 - <=5 | >5 - <=7 | >7 |
B | <=15 | >15 - <=25 | >25 - <=50 | >50 |
C | >75 | <75 - >=50 | <50 - >=25 | <25 |
I want a Calculated Column (Range) in Product table based on the price that fits in the range for specific product
Output Table could be
Product | Price | Range |
A | 6.5 | Range3 |
A | 4.2 | Range2 |
B | 75 | Range4 |
C | 28 | Range3 |
B | 43 | Range3 |
C | 62 | Range2 |
A | 2.2 | Range1 |
Solved! Go to Solution.
Hi, @eswar
I am not sure how your whole data model looks like, but in my opinion, it is good to have the PriceRange-Table structure like below. It is easy in Power Query to change like below.
Then calculated column can be created in an efficient way.
https://www.dropbox.com/s/ycs1w4547kkhhid/eswar.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@eswar You may need to have value in numberic form in your price range table, else it is difficult to perform calculation. There is already a solution for similar problem, please refer to below link, it will take you in the right direction.
https://community.powerbi.com/t5/Desktop/Lookup-Price-Range-for-Style/m-p/322514#M143719
Proud to be a Super User!
Hi, @eswar
I am not sure how your whole data model looks like, but in my opinion, it is good to have the PriceRange-Table structure like below. It is easy in Power Query to change like below.
Then calculated column can be created in an efficient way.
https://www.dropbox.com/s/ycs1w4547kkhhid/eswar.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank you for making the concept (having Min and MAx column for grades) clear.
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |