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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Normalized area map

Hi

 

i'm trying to show density of "people" events per region and it works perfectly fine. The issue however is that these events are not normalized with the density of the population with the region. So for example I have a lot of events in highly populated areas, while few in lower populated - but it might be that we have more events compared to the population.


I have two tables:

Data table: one row per event, with region as a column

Density table: one row per region, with the population in that region as a column

 

I can easily show the map, but how do I show the normalized map?

1 ACCEPTED SOLUTION

Hi @Anonymous

 

So here is what I've done with your data : 

I've created Table1

ID	EventID	Country
1	12334	USA
2	5678	Italy
3	9545	USA
4	4585	France

And Table2

Country	Population
USA	360000000
Italy	60000000
France	67000000

They have a bidirectionnal filter between them

 

Then I create a calculated Table3

 

Table3 = SUMMARIZE(table1;Table1[Country];"Number of events"; counta(Table1[EventID]);"Event/Person";Divide(counta(Table1[EventID]);sum(Table2[Population])))

And add a bidirectionnal filter between Table2 and Table3

 

Then I add a map and here what It looks like : 

 

Country.PNG

 

USA is more faded because it has more inhabitants than France and Italy (both of these country have between 60 and 70 M inhabitants so the color is almost the same)

You need to add Event/Person in the saturation color field and then add Number of Events in the tooltip area (the bottom one called Info-bulles on my screen) so that the end-user can still have it.

 

- Quentin

View solution in original post

17 REPLIES 17
quentin_vigne
Solution Sage
Solution Sage

Hi @Anonymous

 

Maybe you can try to create your own calculation.

As a student in statistics and BI I would create a new var that would the number of event / person 

Divide your number of event by your population and use this new calculation as your variable to show on your map it would be a great indicator

 

- Quentin

Anonymous
Not applicable

Hi quentin, thank you. 

I know how to calculate a normalized value, I just don't know how to do it in BI / PowerBI 🙂 Can you help me? 

@Anonymous

 

Go to the field tab on the right of the screen, right click on the table that have the values for number of inhabitants / events add a new measure

Then a field will appear with 

Measure = 

 

Here you can type the formula you want like if it was in Excel

 

- Quentin

Anonymous
Not applicable

Hi Quentin

 

thank you. I know also how to create a measure.

 

However I have a table that contains one row per Region and another one event per region. The map view works by feeding a set of events and the number of events is the density of the colour. So, for example, USA=100 events, Canada=10 events -> the USA area will be darker.

Now this number is not correlated to the amount of citizens -> USA has 10 times the population of Canada, so the two colours should be the same shade. 

@Anonymous

 

Is the number of inhabitants what you need for each country ? Here is a quick list 

 

 

Rank	Country	Inhabitants
1	 China	1,391,560,000
2	 India	1,333,200,000
3	 United States	327,329,000
4	 Indonesia	261,890,900
5	 Pakistan	211,950,000
6	 Brazil	209,198,000
7	 Nigeria	193,392,517
8	 Bangladesh	164,693,000
9	 Russia	146,877,088
10	 Japan	126,490,000
11	 Mexico	124,286,623
12	 Philippines	106,037,000
13	 Egypt	97,147,000
14	 Ethiopia	94,352,000
15	 Vietnam	93,700,000
16	 Germany	82,740,900
17	 Iran	81,546,800
18	 Democratic Republic of the Congo	81,339,900
19	 Turkey	80,810,525
20	 Thailand	69,037,513
21	 France	67,225,000
22	 United Kingdom	65,648,054
23	 Italy	60,483,973
24	 South Africa	56,717,000
25	 Myanmar	53,370,609
26	 Tanzania	52,554,628
27	 South Korea	51,446,201
28	 Colombia	49,814,600
29	 Kenya	49,699,862
30	 Spain	46,549,045
31	 Argentina	44,044,811
32	 Ukraine	42,322,028
33	 Algeria	41,697,498
34	 Sudan	40,782,742
35	 Poland	38,422,346
36	 Iraq	38,274,618
37	 Uganda	37,673,800
38	 Canada	37,138,500
39	 Morocco	34,700,300
40	 Saudi Arabia	33,413,660
41	 Malaysia	32,559,500
42	 Uzbekistan	32,345,000
43	 Peru	31,826,018
44	 Venezuela	31,431,164
45	 Afghanistan	29,724,323
46	 Ghana	29,614,337
47	 Angola	29,250,009
48	 Mozambique	28,861,863
49	 Nepal	28,825,709
50	 Yemen	28,250,000
51	 Madagascar	25,571,000
52	 North Korea	25,491,000
53	 Australia	24,970,200
54	 Ivory Coast	24,571,044
55	 Cameroon	23,794,164
56	 Sri Lanka	21,444,000
57	 Niger	20,651,070
58	 Romania	19,638,000
59	 Burkina Faso	19,632,147
60	 Mali	18,542,000
61	 Syria	18,270,000
62	 Kazakhstan	18,232,000
63	 Chile	17,574,003
64	 Malawi	17,373,185
65	 Netherlands	17,230,300
66	 Ecuador	17,014,600
67	 Zambia	16,405,229
68	 Guatemala	16,176,133
69	 Cambodia	15,848,495
70	 Senegal	15,726,037
71	 Chad	14,900,000
72	 Somalia	14,742,523
73	 Zimbabwe	14,542,235
74	 Guinea	11,883,516
75	 South Sudan	11,868,209
76	 Rwanda	11,809,300
77	 Tunisia	11,446,300
78	 Belgium	11,406,122
79	 Cuba	11,239,224
80	 Bolivia	11,145,770
81	 Benin	11,002,578
82	 Haiti	10,911,819
83	 Greece	10,768,193
84	 Czech Republic	10,613,350
85	 Burundi	10,400,938
86	 Portugal	10,291,027
87	 Dominican Republic	10,169,172
88	 Jordan	10,175,800
89	 Sweden	10,151,588
90	 Azerbaijan	9,898,085
91	 Hungary	9,771,000
92	 Belarus	9,484,300
93	 United Arab Emirates	9,400,000
94	 Tajikistan	8,931,000
95	 Honduras	8,866,351
96	 Israel	8,869,940
97	 Austria	8,830,487
98	 Switzerland	8,482,200
99	 Papua New Guinea	8,151,300
100	 Togo	7,178,000
101	 Sierra Leone	7,092,113
102	 Paraguay	7,052,983
103	 Bulgaria	7,050,034
104	 Serbia	7,040,272
105	 El Salvador	6,581,940
106	 Laos	6,492,400
107	 Libya	6,374,616
108	 Nicaragua	6,305,956
109	 Kyrgyzstan	6,140,200
110	 Lebanon	6,082,000
111	 Denmark	5,785,864
112	 Turkmenistan	5,758,000
113	 Singapore	5,612,300
114	 Finland	5,513,902
115	 Slovakia	5,443,120
116	 Norway	5,302,778
117	 Republic of the Congo	5,260,750
118	 Eritrea	5,068,831
119	 Costa Rica	4,947,490
120	 New Zealand	4,885,320
121	 Palestine	4,816,503
122	 Ireland	4,792,500
123	 Central African Republic	4,659,080
124	 Oman	4,639,678
125	 Liberia	4,289,520
126	 Croatia	4,154,213
127	 Panama	4,098,135
128	 Kuwait	4,082,704
129	 Mauritania	3,806,719
130	 Georgia	3,729,600
131	 Moldova	3,550,900
132	 Bosnia and Herzegovina	3,511,372
133	 Uruguay	3,493,205
134	 Mongolia	3,217,175
135	 Armenia	2,972,900
136	 Albania	2,870,324
137	 Lithuania	2,800,830
138	 Jamaica	2,728,864
139	 Qatar	2,685,053
140	 Namibia	2,368,747
141	 Lesotho	2,233,339
142	 Botswana	2,230,905
143	 The Gambia	2,101,000
144	 Macedonia	2,073,702
145	 Slovenia	2,066,880
146	 Gabon	2,025,137
147	 Latvia	1,923,500
148	 Guinea-Bissau	1,553,822
149	 Bahrain	1,451,200
150	 Trinidad and Tobago	1,356,633
151	 Estonia	1,319,133
152	 Mauritius	1,264,887
153	 Equatorial Guinea	1,222,442
154	 East Timor	1,167,242
155	 Swaziland	1,145,970
156	 Djibouti	956,985
157	 Fiji	884,887
158	 Cyprus	854,8
159	 Comoros	806,153
160	 Bhutan	801,14
161	 Guyana	777,859
162	 Solomon Islands	651,7
163	 Montenegro	622,387
164	 Luxembourg	602,005
165	 Suriname	541,638
166	 Cape Verde	537,661
167	 Malta	434,403
168	 Brunei	422,678
169	 Belize	387,879
170	 Bahamas	382,46
171	 Iceland	350,71
172	 Maldives	344,023
173	 Vanuatu	289,7
174	 Barbados	285,719
175	 Samoa	197,448
176	 São Tomé and Príncipe	187,356
177	 Saint Lucia	178,844
178	 Kiribati	115,3
179	 Saint Vincent and the Grenadines	109,557
180	 Federated States of Micronesia	104,6
181	 Grenada	103,328
182	 Tonga	103,252
183	 Seychelles	94,205
184	 Antigua and Barbuda	86,295
185	 Andorra	78,264
186	 Dominica	71,293
187	 Marshall Islands	55
188	 Saint Kitts and Nevis	46,204
189	 Monaco	38,3
190	 Liechtenstein	38,111
191	 San Marino	33,326
192	 Palau	17,8
193	 Tuvalu	10,782
194	 Nauru	10,084
195	 Vatican City	800

- Quentin

Anonymous
Not applicable

Hi Quentin

 

I have the number of inhabitants. It's written in the initial post.


I have TWO tables

ONE is made of one event of my occurence, with his region attached. Example:

ID - eventid - Country

1 - 1234 - USA

2 - 3382 - Italy

3 - 2744 - USA

ecc. ecc.

 

TWO is basically the table you pointed

Region - Population

USA - 360M

Italy - 60M 

ecc. ecc.

 

I do not use ArcGis map, but the standard Coloured Map of PowerBi which does not have that feature like arcgis. I pointed that article to show that what I'm asking is a common issue.

 

@Anonymous

 

Ok, it's more easy to understand like this. 
First of all, I would create a new table to see things clearer 

 

This table is the number of event and the event per person grouped by country names

 

Table = SUMMARIZE(Table1;Table1[Country];"Number of events";COUNTA(Table1[EventId]);"Event/Person";DIVIDE(counta(Table1[EventId]) ; sum(Table2[Population)))

Hope this is what you are looking for 

 

- Quentin

Anonymous
Not applicable

Hi Quentin

 

thank you. It does partially solve my issue. There are two caveats:

- The table data does not consider filters. If I have filters in the page, and I show the table data, the filters does not filter out data. The table consider only the entire data set:

 

(i.e. the orange filters does not change the red values)

The second issue is that I cannot feed this data to the map control as it accepts only a set of events. 

The more I think about the more I believe I cannot do it with this control....

@Anonymous

 

Did you create a link between the tables in the relationship tab ? If there is not relationship there will be no filter working

 

On your map you need to add to RegionLocal to the field 'Place' (or something with this name) and Number of events in the value/color saturation field 

 

- Quentin

Anonymous
Not applicable

And by the way, I already have the map working, but it's not normalized:

 

Anonymous
Not applicable


@quentin_vigne wrote:

@Anonymous

 

Did you create a link between the tables in the relationship tab ? If there is not relationship there will be no filter working

 

On your map you need to add to RegionLocal to the field 'Place' (or something with this name) and Number of events in the value/color saturation field 

 

- Quentin


On the filtering, you're right, I didn't link the new Summary Table RegionLocal with the original table, and indeed now works. Well, "works" -> Works only if I filter on Regionlocal, but for example if I filter by period, numbers does not change.

 

This is my link status after your suggestion:

 

 

 

If chose "Regione" filter, and I choose only one region (i.e. "Abruzzo Italia") the table shows correctly only one row.
However the column "number of events" reports always the same number, regardless of the time period that I chose on the top filters.

@Anonymous

 

You need to edit the relation between Italy + All and Density and choose bi directionnal filter

You only have a filter from Density to Italy + All so you can't filter the number of event by choosing a date from Italy + All (Hope I'm clear)

 

On the map you need to display the events/person + Region local (I told you Number of events but that's a mistake from me ...)

 

- Quentin

Anonymous
Not applicable

Hi, this is how it's set and it's not working

(sorry for the italian, but "entrambi" means bidirectional.)

 

Also on the map: as far as i know it expects a number of countable events and a region on each one of them, instead of a row per region with a value...

 

 

Hi @Anonymous

 

So here is what I've done with your data : 

I've created Table1

ID	EventID	Country
1	12334	USA
2	5678	Italy
3	9545	USA
4	4585	France

And Table2

Country	Population
USA	360000000
Italy	60000000
France	67000000

They have a bidirectionnal filter between them

 

Then I create a calculated Table3

 

Table3 = SUMMARIZE(table1;Table1[Country];"Number of events"; counta(Table1[EventID]);"Event/Person";Divide(counta(Table1[EventID]);sum(Table2[Population])))

And add a bidirectionnal filter between Table2 and Table3

 

Then I add a map and here what It looks like : 

 

Country.PNG

 

USA is more faded because it has more inhabitants than France and Italy (both of these country have between 60 and 70 M inhabitants so the color is almost the same)

You need to add Event/Person in the saturation color field and then add Number of Events in the tooltip area (the bottom one called Info-bulles on my screen) so that the end-user can still have it.

 

- Quentin

Anonymous
Not applicable

Hi Quentin, thanks to your insights and other info, i've been able to make it work. The culprit was the fact that I expected the map control to work with countable events instead of a value per region.

 

Thanks:

 

@Anonymous

 

Oh you are using Arcgis Map ! Well the option to Normalize doesn't exist in PowerBI but only in ArcGIS Online so you will have to do it manually and add the number of inhabitants for each country you have

 

 -Quentin

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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