Filled Map by regions by value

[Anonymous]

Hi, I am trying to create a filled map that color goes from darker blue to lighter blue depends on the regions' sales number, eg MD/PA/VA are Region1, ND/SD/MT are region2, region1 has higher sales number than region2, so the color on MD/PA/VA will be shaded in the same darker blue color while ND/SD/MT are shaded in the same lighter blue color.  I have the state, region, and sales column in the table.  When i create the map with state and sales, it shows correctly but only on state level.  as soon as I add the region, it stopped reflecting sales value, only showing regions with default color.  I have tried to use color fx under legend but no luck.  How do i fix this?  thanks in advance!

[Thejeswar · Accepted solution]

Hi,

Hope you are using Filled Map Visual. In such case, there is a option to conditionally format the colors in the map. You should be using the Sales Revenue measure to conditionally format the map visual. Color Scales or Divergent Colour ... you can choose as per your need

PFB link the shows the step by step implementation of conditional formatting that would avoid setting the colors manually for all regions

https://thewindata.com/power-bi-desktop-filled-maps-with-conditional-formatting/

Hope this helps!!

If this is what you are expecting, mark this as an answer!!!

[Thejeswar]

@Anonymous ,

Can you share a sample PBIX file?

[Anonymous]

Hi @Thejeswar  Please see the screenshots below.  to simplify, I only inluded few states in R1 and R2.  The real data would be for the entire 50states. 

sample Dataset

I created a new measure [Total sale per region]

as you can see, in the table, it shows total sale per region, but I am having a difficult time to reflect this in the map -- R1 should have darker blue color while R2 sould have lighter blue color, but I don't see where I can set that.  Since the real data will have more regions and more states, I don't want to manually set this in Data colors, hope there is a function I can use.  Thanks much!  

Christine

[AllisonKennedy · Accepted solution]

@Anonymous  Please paste your sample data in table format in future as it makes it easier for us to build you a sample file like the one attached below signature.

You need to build a DAX measure that calculates the Sales per region, and use that in your map formatting: 

Sales All States Current Region =

VAR _Region = SELECTEDVALUE(Sales[Region])

RETURN

CALCULATE(

[Total Sales]

, ALL(Sales[State]), Sales[Region] = _Region

)

[Anonymous]

Hi @AllisonKennedy this is very helpful!  after seeing your DAX, i realized my sales by region measure was wrong.  thank you so much!

[Thejeswar · Accepted solution]

Hi,

Hope you are using Filled Map Visual. In such case, there is a option to conditionally format the colors in the map. You should be using the Sales Revenue measure to conditionally format the map visual. Color Scales or Divergent Colour ... you can choose as per your need

PFB link the shows the step by step implementation of conditional formatting that would avoid setting the colors manually for all regions

https://thewindata.com/power-bi-desktop-filled-maps-with-conditional-formatting/

Hope this helps!!

If this is what you are expecting, mark this as an answer!!!

[AllisonKennedy]

@Anonymous It sounds like you don't need State in your map at all in this case - only region in location. 

[Anonymous]

If I take the state out, PBI wouldn't know what regions are since they are customized regions.  the map will just show completely blank.