Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi
My data structure is as follow :
order id sale_price
1 $35
2 $22
3 $7
I am looking for a way to create calculate column with Dax to creeate dynamic sale price range and to get a table like below
order id new calculate field
1 $30-40
2 $20-30
3 $5-10
Thanks
Solved! Go to Solution.
The following should do the sort of thing you want:
= SWITCH(TRUE(), Table1[sale_price] < 5, "$<5", AND(Table1[sale_price]>=5, Table1[sale_price]<10), "$05-10", AND(Table1[sale_price]>=10, Table1[sale_price]<20), "$10-20", AND(Table1[sale_price]>=20, Table1[sale_price]<30), "$20-30", AND(Table1[sale_price]>=30, Table1[sale_price]<40), "$30-40", "$40+")
But, for a more robust solution that is maintainable, sortable etc., see posts such as https://blogs.msdn.microsoft.com/analysisservices/2014/06/05/bucketing-values-in-dax/ and the Marco Russo links in the comments at the end of tha tblog.
The DAX solution provided by Steve_Wheeler should work. We can also do it with power query in Query Editor.
let Source = Excel.Workbook(File.Contents("C:\How to create dynamic data bin using Dax.xlsx"), null, true), Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"order id", Int64.Type}, {"sale_price", Int64.Type}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [sale_price] > 5 and [sale_price] < 10 then "$5-10" else if [sale_price] > 20 and [sale_price] < 30 then "$20-30" else if [sale_price] > 30 and [sale_price] < 40 then "$30-40" else null) in #"Added Conditional Column"
Best Regards,
Herbert
The following should do the sort of thing you want:
= SWITCH(TRUE(), Table1[sale_price] < 5, "$<5", AND(Table1[sale_price]>=5, Table1[sale_price]<10), "$05-10", AND(Table1[sale_price]>=10, Table1[sale_price]<20), "$10-20", AND(Table1[sale_price]>=20, Table1[sale_price]<30), "$20-30", AND(Table1[sale_price]>=30, Table1[sale_price]<40), "$30-40", "$40+")
But, for a more robust solution that is maintainable, sortable etc., see posts such as https://blogs.msdn.microsoft.com/analysisservices/2014/06/05/bucketing-values-in-dax/ and the Marco Russo links in the comments at the end of tha tblog.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
56 | |
56 | |
38 | |
29 |
User | Count |
---|---|
75 | |
62 | |
45 | |
40 | |
39 |