Analyzing Monthly Population Data

[DAX_pbi_learner]

How to visualize monthly population data?

7m ago

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.

[lbendlin]

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.