Forum Discussion

Giovani's avatar
Giovani
Icon for Helper I rankHelper I
7 years ago

Customized state region in a shape map

Hi, 

 

I am trying to display region information on a shape map using diverging colors. But what should be straight forward is getting difficult nasty, I am probably missing some basic step.

I want to customize information for US states displaying some states as individual information and other as regional information. Here is an example of the data.

MonthUS-StateRegionValue
JanuaryGAGA20
JanuaryALRegion 11
JanuaryMSRegion 12
JanuaryLARegion 13
JanuaryARAR30
JanuarySCRegion 275
JanuaryNCRegion 225
FebruaryGAGA10
FebruaryALRegion 130
FebruaryMSRegion 140
FebruaryLARegion 180
FebruaryARAR1
FebruarySCRegion 25
FebruaryNCRegion 24

 

An example of what I would like to do is for Region 2 where in January I would like to have both US-state SC and NC grouped and colored with the "maximum" diverging color selected, but when filtering February I would like to see it with the "minimum" diverging color.

In both cases, I don`t want to have individual state "value" informed, only the final sum with the Region name.

 

Any idea of how I could overpass this issue.

 

Thanks

Need Help

2 Replies

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Giovani 

     

    You can create column Sum for Region to get total value for each region and month.

     

    Sum for Region = CALCULATE(SUM(Table1[Value]),FILTER(ALLSELECTED(Table1),Table1[Region]=EARLIER(Table1[Region])&&Table1[Month]=EARLIER(Table1[Month])))

     

     

     

     

     

     

     

     

     

    Then, create shape map visual setting like picture below. Drill down to see the total value for each region and month.

     

     

     

     

     

     

     

     

     

     

     

    Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ea_D-qma2fZFkHg2nO77L0gB8WjoABl0DmUiB3TPC4VHQg?e=hjG3Xc

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Giovani's avatar
      Giovani
      Icon for Helper I rankHelper I
      7 years ago

      Hi Amy,

       

      Sounds very interesting, but the region is displayed now on top of Canada and not more on top of original US states. Any idea how to solve that?