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 regi...
  • 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

quentin_vigne's avatar
quentin_vigne
Icon for Solution Sage rankSolution 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
      Icon for Solution Sage rankSolution 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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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.