Reply
avatar user
Anonymous
Not applicable
Partially syndicated - Outbound

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

 

christianadaa_0-1653448592701.png

christianadaa_1-1653448643650.png

 

@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 

 

1 ACCEPTED SOLUTION

Syndicated - Outbound

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

14 REPLIES 14
Ashish_Mathur
Super User
Super User

Syndicated - Outbound

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
avatar user
Anonymous
Not applicable

Syndicated - Outbound

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

Syndicated - Outbound

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
avatar user
Anonymous
Not applicable

Syndicated - Outbound

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: 

christianadaa_1-1654144934478.png

Range: 

christianadaa_0-1654144826515.png

 

Then, I manually grouped this range into groups. 

christianadaa_2-1654145029719.png

 

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

christianadaa_3-1654145083963.png

 

I hope there is a way!

 

Christiana

 

 

Syndicated - Outbound

I cannot help without a file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
avatar user
Anonymous
Not applicable

Syndicated - Outbound

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? 

christianadaa_0-1655686713569.png

 

How do I attach a file to this thread? 

 

Christiana

Syndicated - Outbound

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
avatar user
Anonymous
Not applicable

Syndicated - Outbound

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
avatar user
Anonymous
Not applicable

Syndicated - Outbound

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? 

 

christianadaa_0-1655784891287.png

christianadaa_1-1655784986034.png

 

What is incorrect on my end? 

Syndicated - Outbound

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
avatar user
Anonymous
Not applicable

Syndicated - Outbound

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

Syndicated - Outbound

You are welcome.  That is correct.  


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
avatar user
Anonymous
Not applicable

Syndicated - Outbound

Thank you very much!

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)