Customized state region in a shape map

[Giovani]

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.

Month	US-State	Region	Value
January	GA	GA	20
January	AL	Region 1	1
January	MS	Region 1	2
January	LA	Region 1	3
January	AR	AR	30
January	SC	Region 2	75
January	NC	Region 2	25
February	GA	GA	10
February	AL	Region 1	30
February	MS	Region 1	40
February	LA	Region 1	80
February	AR	AR	1
February	SC	Region 2	5
February	NC	Region 2	4

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

[v-xicai]

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-qma2fZFkHg2nO...

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]

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?