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.
Hi Ashish,
Thank you. Please see file attached.
I would like too create groups for the column 'original contract value (groups)' that reflect the ranges in the pie chart below. These groups will be auto-updated instead of manually updated in the pie graph. Is this possible?
How do I attach a file to this thread?
Christiana
Hi,
Upload the file to Google Drive and share the download link.
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.
Please see link below.
https://drive.google.com/file/d/1q4wj30g8gLUtfuy11cDUG9BTFr2qM1fp/view?usp=sharing
Thank you.
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!