Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Group rows within some categories

Hello,

 

I have a table like below:

 

District             ||      Age      ||      Year       ||  Population

Jacksonville      ||        0        ||      2017       ||   4891

............               ||        1        ||      2017       ||   7910

....................

 

Age runs from 0 until 90, Year runs from 2017 until 2020.

 

I want to sum up population grouped into range of certain years years of ages for each district and year. For example, from 0-5, 5-10, 10-15, 16-18, 18-19, 20-25, etc.

 

Example of desired outcome:

 

District          ||     Age    ||  Year    || Population

Jacksonville  ||    0-5      || 2017    ||  12900

Jacksonville  ||     5-10   || 2017    ||   28001

....................

Jacksonville  ||     18-19   || 2017    ||   7876

..............

Jacksonville  ||     75+      || 2017    ||  35984

Miami           ||     0-5       ||  2017   ||  17803

.............

 

How can I achieve this? 

 

Thank you in advance!

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

Ok.  Since your bins don't seem to be the same size, you can't do a simple rounding to get them.  The simplest way might be to make a table with 90 rows for each age and their associate bin, and merge that into your query on the Age column.  You can then do a Group By step to get the sum of the newly added Bin column.

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
mahoneypat
Microsoft Employee
Microsoft Employee

Ok.  Since your bins don't seem to be the same size, you can't do a simple rounding to get them.  The simplest way might be to make a table with 90 rows for each age and their associate bin, and merge that into your query on the Age column.  You can then do a Group By step to get the sum of the newly added Bin column.

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

Hello Pat,

 

I did as instructed and it has worked well for me. Thanks! 🙂

 

Cheers,

 

P-

mahoneypat
Microsoft Employee
Microsoft Employee

You could do this with the Group/Bin column.  Please see this article.  Doing it in the query editor, would be harder (but doable) and would limit the analyses you can do with your dataset.

Use grouping and binning in Power BI Desktop - Power BI | Microsoft Docs

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

Hi Pat,

 

Thank you a lot for your information. However I want this to be done with query editor because there is another table that I want to create relationship with this table. That table is from another data source and have the ranges in it so it's best that I get this done. Can you help with the Query Editor?

 

Cheers,

 

P-

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.