Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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?

  • quentin_vigne's avatar
    quentin_vigne
    8 years ago

    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

17 Replies

  • quentin_vigne's avatar
    quentin_vigne
    Solution Sage
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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? 

      • quentin_vigne's avatar
        quentin_vigne
        Solution Sage
        8 years ago

        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