Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hello Pat,
I did as instructed and it has worked well for me. Thanks! 🙂
Cheers,
P-
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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-
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.