Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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 :
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
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
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
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
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
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
And by the way, I already have the map working, but it's not normalized:
@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
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 :
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
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
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |