Automatic grouping data by range

[Anonymous]

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 dataset?

i.e every time data is added in the range of 250K-680K it will be automatically grouped under the range 250K-680K. Please see below screenshots. 

@Nathaniel_C - you were a brilliant help in my previous post re data ordering- i would be greatly appreciative if you have any insight to this issue i am facing 

[Ashish_Mathur]

Hi,

Share the download link of your PBI file and clearly show the grouping that you want to create.

[Anonymous]

Hi Ashish, 

Thank you for your response. 

I can not share the download link due to privacy reasons. 

Please see my above reply that includes more detail re your request. Otherwise I am happy to provide more information if you specifiy. 

Thank you, 

Christiana

[Ashish_Mathur]

Hi,

Anonymise your data and share the download link of that file.

[Anonymous]

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]

I cannot help without a file.

[Anonymous]

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

[Ashish_Mathur]

Hi,

Upload the file to Google Drive and share the download link.

[Anonymous]

Please see link below. 

https://drive.google.com/file/d/1q4wj30g8gLUtfuy11cDUG9BTFr2qM1fp/view?usp=sharing 

Thank you. 

[Ashish_Mathur · Accepted solution]

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]

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? 

[Ashish_Mathur]

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). 

[Anonymous]

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

[Ashish_Mathur]

You are welcome.  That is correct.  

[Anonymous]

Thank you very much!