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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Ashish, 

 

Thank you.

 

I have checked with my organisation and unable to share data even if it is anon. 

 

Please see below screenshots of the data. 

 

First, I created a range in power query. 

Original: 

Range: 

 

Then, I manually grouped this range into groups. 

 

My goal is to make this grouping automatic insteaf of manually dragging it. The manual process is captured below. 

 

I hope there is a way!

 

Christiana

 

 

Ashish_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

I cannot help without a file.