Forum Discussion
Creating number ranges with Dax
Hello,
I have a Dataset that has a "Weight" column with numerical values representing weights.
For example :
| 267890 |
| 225098 |
| 194682 |
| 224534 |
| 258943 |
| 262306 |
| 271780 |
and also a "Maximum weight" column which represents the maximum weight allowed:
| 272000 |
| 272000 |
| 272000 |
| 272000 |
| 272000 |
| 272000 |
| 272000 |
I have created two measures:
Minimum = CALCULATE(FLOOR(MIN(Table[Weight])/1000,1)*1000) which allows me to round the lowest value down to the hundredth (here 194682 so I get 194500)
And Maximum = the Maximum weight value.
Between these two values, I want to create ranges of 2500, for example :
194500 - 197000
197000 - 199500
...
269500 - 272000
To associate each weight with a range, and count the number of weights in each range.
For example :
| 196782 | 194500 - 197000 |
Would you please know how to do this? I admit I've been struggling for a while...
Thank you.
You can create a series like this:
VAR _Max = 272000 VAr _Incr = 2500 VAR _Min = INT ( MIN ( Table1[Weight] ) / _Incr ) * _Incr RETURN GENERATESERIES ( _Min, _Max, _Incr )If you want it just as a calculated column on your existing table, then try:
Range = VAr _Incr = 2500 VAR _Min = INT ( Table1[Weight] / _Incr ) * _Incr VAR _Max = MIN ( 272000, _Min + _Incr ) RETURN _Min & " - " & _Max
1 Reply
You can create a series like this:
VAR _Max = 272000 VAr _Incr = 2500 VAR _Min = INT ( MIN ( Table1[Weight] ) / _Incr ) * _Incr RETURN GENERATESERIES ( _Min, _Max, _Incr )If you want it just as a calculated column on your existing table, then try:
Range = VAr _Incr = 2500 VAR _Min = INT ( Table1[Weight] / _Incr ) * _Incr VAR _Max = MIN ( 272000, _Min + _Incr ) RETURN _Min & " - " & _Max