Forum Discussion
Automatic grouping data by range
Hi,
Create a table like this and name it Buckets
Lower Upper Bracket 0 30,000 0 - 30K 30,000 2,50,000 30 - 250K 2,50,000 6,80,000 250 - 680K 6,80,000 10,00,000 680K - 1M 10,00,000 10,00,00,000 > 1M Write this calculated column formula
Column = CALCULATE(MAX(Buckets[Bracket]),FILTER(Buckets,Buckets[Lower]<='Table'[original contract value]&&Buckets[Upper]>='Table'[original contract value]))Hope this helps.
Please see link below.
https://drive.google.com/file/d/1q4wj30g8gLUtfuy11cDUG9BTFr2qM1fp/view?usp=sharing
Thank you.
Hi,
Create a table like this and name it Buckets
| Lower | Upper | Bracket |
| 0 | 30,000 | 0 - 30K |
| 30,000 | 2,50,000 | 30 - 250K |
| 2,50,000 | 6,80,000 | 250 - 680K |
| 6,80,000 | 10,00,000 | 680K - 1M |
| 10,00,000 | 10,00,00,000 | > 1M |
Write this calculated column formula
Column = CALCULATE(MAX(Buckets[Bracket]),FILTER(Buckets,Buckets[Lower]<='Table'[original contract value]&&Buckets[Upper]>='Table'[original contract value]))Hope this helps.
Hi,
Mine is a calculated column formula to be written in the Data Model (not an M language code to be written in the Query Editor).
You are welcome. That is correct.
Thank you so much for your response.
I tried to imitate this and recieved the error 'Token Literal expected' - the error is with 'Table' where the grey highlight and red mark is in the second screesnhot. Please see below. screenshots?
What is incorrect on my end?
Thank you very much.
This worked so well. I have applied it to my main report.
Just to confirm - this formula is only using the original contract value column and not the (groups) column?
Christiana
Thank you very much!