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.
Hi Everyone,
I'm having a touch time figuring out how to visualize a dataset that has monthly population and demographic data by city in a state. Here's an example of the data I'm dealing with:
For each month of a given year, there are population counts by municipality, education level, race. My issue is, when I try to use charts, Power BI automatically takes the 'Sum of Count' which is not what I want to see. Instructions on removing the summarization for variables is not working.
year | month | citycode | race | education | count | Quarter | DateforPowerBI |
2020 | 1 | 1 | sa | hs | 99 | Q1 | 1/1/2020 |
2020 | 1 | 2 | ea | hs | 18 | Q1 | 1/1/2020 |
2020 | 1 | 3 | ca | hs | 13 | Q1 | 1/1/2020 |
2020 | 1 | 4 | aa | hs | 11 | Q1 | 1/1/2020 |
2020 | 1 | 5 | pi | hs | 6 | Q1 | 1/1/2020 |
2020 | 1 | 6 | sa | hs | 26 | Q1 | 1/1/2020 |
2020 | 1 | 7 | ea | hs | 840 | Q1 | 1/1/2020 |
2020 | 1 | 8 | ca | hs | 5 | Q1 | 1/1/2020 |
2020 | 1 | 9 | aa | hs | 2 | Q1 | 1/1/2020 |
2020 | 1 | 10 | pi | hs | 35 | Q1 | 1/1/2020 |
2020 | 1 | 11 | sa | hs | 2 | Q1 | 1/1/2020 |
2020 | 1 | 12 | ea | hs | 4 | Q1 | 1/1/2020 |
2020 | 1 | 13 | ca | hs | 2 | Q1 | 1/1/2020 |
2020 | 1 | 14 | aa | hs | 10 | Q1 | 1/1/2020 |
2020 | 1 | 15 | pi | hs | 0 | Q1 | 1/1/2020 |
2020 | 1 | 16 | sa | hs | 11 | Q1 | 1/1/2020 |
2020 | 1 | 17 | ea | hs | 9 | Q1 | 1/1/2020 |
2020 | 1 | 18 | ca | hs | 2 | Q1 | 1/1/2020 |
2020 | 1 | 19 | aa | hs | 160 | Q1 | 1/1/2020 |
2020 | 1 | 20 | pi | hs | 199 | Q1 | 1/1/2020 |
2020 | 1 | 21 | sa | hs | 121 | Q1 | 1/1/2020 |
2020 | 1 | 22 | ea | hs | 4 | Q1 | 1/1/2020 |
2020 | 1 | 23 | ca | hs | 448 | Q1 | 1/1/2020 |
2020 | 1 | 24 | aa | hs | 19 | Q1 | 1/1/2020 |
2020 | 1 | 25 | pi | hs | 4 | Q1 | 1/1/2020 |
To give you a more accurate description of the data, when it's aggregated by month for one calendar year, you can see that each month has the total population of the state which changes each month (as people migrate in and out). However, when I try to use a matrix or visual I can't seem to stop it from using a calculation and it ends up summing the count which again inflates the total.
year | MONTH | population |
2020 | 1 | 5,267,026.00 |
2020 | 2 | 5,561,676.00 |
2020 | 3 | 5,461,072.00 |
2020 | 4 | 5,412,552.00 |
2020 | 5 | 5,419,980.00 |
2020 | 6 | 5,455,485.00 |
2020 | 7 | 5,460,272.00 |
2020 | 8 | 5,463,453.00 |
2020 | 9 | 5,466,835.00 |
2020 | 10 | 5,460,052.00 |
2020 | 11 | 5,420,165.00 |
2020 | 12 | 5,242,794.00 |
The goal is to look at migration patterns into and out of cities over months in a calendar year and other calendar years(I have two more years of data) by education level and demographic. If you have any ideas or examples I can look at please send them over. I'm currently not trying to do a spatial analysis, I want to use a matrix table and line charts. I haven't seen any exampels covering a topic or dataset like this. Thank you for any help/advice that you can give me. I had to anonymize the data, let me know if you have any questions.
The goal is to look at migration patterns
for that you need source and destination information, and a Sankey diagram or similar.
If you you only want to show the comings and goings per city you could think of using a waterfall visual and small multiples.
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.
User | Count |
---|---|
6 | |
1 | |
1 | |
1 | |
1 |
User | Count |
---|---|
8 | |
2 | |
1 | |
1 | |
1 |