Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Automatic grouping data by range

I have manually grouped the data as seen in the below screenshots.    Is there a way to automatically group this data so I do not have to go back and edit every time new data is added into my datas...
Need Help
Show and Tell
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    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.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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?