Forum Discussion
How to compare numeric ranges?
For example I would like to compare the sales qty of items with price tag range of 0 -1000, 1001-10000, > 10000. Could someone give me a direction on this? Thank you in advance.
If the groupings are largely static, you could create a groupings column as part of your import. In your case it would be something as simple as adding column in the Edit Queries section of your Power BI. Add this to the table with your price tags and use code similar to this:
if [PriceTag] > 1000 then if [PriceTag] > 10000 then "> 10000" else "1001 - 10000" else "0 - 1000")
4 Replies
If the groupings are largely static, you could create a groupings column as part of your import. In your case it would be something as simple as adding column in the Edit Queries section of your Power BI. Add this to the table with your price tags and use code similar to this:
if [PriceTag] > 1000 then if [PriceTag] > 10000 then "> 10000" else "1001 - 10000" else "0 - 1000")
Thank you for you help. May I ask a how to define the range in a table and use it?
For example if a custom [PriceTag Range] table and would like to make a calculated column base on it, how can I do that? I tried using IF operator but I don't know how to compare it to other table's column.
It would need to be a calculated column. I would then have a DAX statement that does the following:
Range = VAR lineValue = [Value] RETURN CALCULATE( FIRSTNONBLANK( 'Range Table'[Custom Range], 1 ), 'Range Table'[From] <= lineValue, 'Range Table'[To] >= lineValue )Naturally you will need to make sure to pick the correct table and field names.
Also you will need to ensure your range covers all possible values. In the sample table you provided, you have a gap between 2,000 & 4,000, and 10,000, & 50,000.
