Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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.
Hi,
Share the download link of your PBI file and clearly show the grouping that you want to create.
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
Hi,
Anonymise your data and share the download link of that file.
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
I cannot help without a file.
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.
Please see link below.
https://drive.google.com/file/d/1q4wj30g8gLUtfuy11cDUG9BTFr2qM1fp/view?usp=sharing
Thank you.
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.
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?
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).
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
You are welcome. That is correct.
Thank you very much!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
82 | |
65 | |
63 | |
56 |
User | Count |
---|---|
171 | |
111 | |
109 | |
72 | |
71 |