Power BI Map - Sales Data

[dokat]

Hi,

I am trying to map out my sales data with power bi. Below is the format of my census division data. I dont have longtidue or lattiudue . Based on below census divisions is it possible to map out my data in power bi?

Census Division	Measures	Values
New England	Dollars	$13,024
Middle Atlantic	Dollars	$11,044
East North Central	Dollars	$22,402
West North Central	Dollars	$13,233
South Atlantic	Dollars	$17,095
East South Central	Dollars	$13,997
West South Central	Dollars	$14,064
Mountain	Dollars	$10,279
Pacific	Dollars	$7,849
South Atlantic	Dollars	$710
East South Central	Dollars	$391
New England	Dollars	$1,899
Middle Atlantic	Dollars	$4,363
East North Central	Dollars	$8,963
West North Central	Dollars	$3,282
South Atlantic	Dollars	$5,818
East South Central	Dollars	$1,677
West South Central	Dollars	$2,318

[TheoC · Accepted solution]

@dokat 

Grab the JSON files for the US census regions, add them to your shape, and you've got exactly what you need:

For US maps:  https://github.com/d3/d3/wiki/gallery/#maps

(original source: https://community.powerbi.com/t5/Desktop/US-states-Shape-Map-JSON-file/m-p/79071)

This should resolve the problem 🙂

[TheoC · Accepted solution]

@dokat check this link out: https://docs.microsoft.com/en-us/power-bi/visuals/desktop-shape-map

[TheoC · Accepted solution]

@dokat it should be one of the following: https://www.census.gov/geographies/mapping-files/time-series/geo/carto-boundary-file.html

Try one of the Nation or Region zips.

Cheers

Theo

[TheoC · Accepted solution]

@dokat you may need to convert to JSON: https://www.statsilk.com/maps/convert-esri-shapefile-map-geojso

[TheoC]

Hi @dokat 

Given the limited data accuracy, it is likely you'd have some inaccurate points on the respective map.  You could amend slightly to allocate a State and Country to help mitigate these.

The below is what you will end up with if you're using the Map visual.

As mentioned, there will be some limitations.

All the best.

Theo

[dokat]

@TheoC  yes i got the same resuls. I wonder if i assign states to each region if that would work. rather than map out the census devision map will be based on states.

[TheoC]

Hi @dokat 

Yes, that would work well.  You could then switch from Map visual to Shape / Filled visual and limit it to the US states at a shape level. Add a few little options within the visual menu, and it would look great!

Cheers mate! Well done.

Theo

[dokat]

@TheoC  In theory it works however how will i establish one to many relation. 1 census division may have 6 or 7 states listed under it and i dont have state level sales data. Sales data is at census region level.

I also have DMA data i wonder if there is way to plot based on that rather than US census or divsion data.

Thanks

[TheoC · Accepted solution]

@dokat 

Grab the JSON files for the US census regions, add them to your shape, and you've got exactly what you need:

For US maps:  https://github.com/d3/d3/wiki/gallery/#maps

(original source: https://community.powerbi.com/t5/Desktop/US-states-Shape-Map-JSON-file/m-p/79071)

This should resolve the problem 🙂

[dokat]

@TheoC  Thank you for sharing the github link however when i try to download file link broken link and doesnt work. Do you know of any other source that has the files

[TheoC]

@dokat try this one:

https://github.com/johan/world.geo.json/tree/master/countries/USA

[TheoC]

@dokat actually, scratch the previous link. Use this one, directly from the US Census site: https://www.census.gov/geographies/mapping-files/time-series/geo/carto-boundary-file.html

[dokat]

@TheoC thank you for sharing the link from census. How do i incorporate to the shape map?

[TheoC · Accepted solution]

@dokat check this link out: https://docs.microsoft.com/en-us/power-bi/visuals/desktop-shape-map

[dokat]

@TheoC  I am following the instructions in the link. When i download the JSON file from US Census for US Dvisions it is in below format. Non of the flies are in .json format.

[TheoC · Accepted solution]

@dokat it should be one of the following: https://www.census.gov/geographies/mapping-files/time-series/geo/carto-boundary-file.html

Try one of the Nation or Region zips.

Cheers

Theo

[dokat]

@TheoC I did however none of the files in zip is in .JSON format.

Please see below formats in the zip files.  Thanks

[TheoC · Accepted solution]

@dokat you may need to convert to JSON: https://www.statsilk.com/maps/convert-esri-shapefile-map-geojso

[dokat]

@TheoC I got map to work. I had to convert the data to TopoJson format before importing and use custom map option. My challange right now is state of alaska shows in the map which i'd like to exclude.

[dokat]

@TheoC Thank you i converted the files to GEOJson format and import it to power BI. However when i put it in location it dollars (values) under color saturation it change the map. I put . What may cause this issue? Below is the screenshot

[TheoC]

Hi @dokat 

A few things can cause this but is likely stemming from a change to excluding Alaska?  The other factor could be the values and their format?  Are you using a measure for the values (i.e. SUM ( Table[Amount] ) or pulling straight from the field pane?  

Cheers mate.

Theo 

[dokat · Accepted solution]

@TheoC  I am using a measure not pulling it from field pane. I went back to the original plan, brought in US States Geo Json file into power bi. Created a table with Census Division and States and linked it to the source data.  I have below formula to calculate the average sum for each census divison. It worked for me ut hd to get creative. I couldnt have done without help of @TheoC. Thank you! 

POS Sales = var _currentCensus = MAX(Region[Census Division])
var _sum = SUM(Region[Values])
var _uniquestates = CALCULATE(DISTINCTCOUNT(Map[State]),
    Region[Census Division]=_currentCensus)

var _calculation = DIVIDE(_sum,_uniquestates)
return _calculation

[TheoC]

@dokat the map looks sensational mate!  Well done on getting there.  In all honesty, I think you could put together your first blog post on the Community about how you went through this process; a step-by-step guide on this.  Many members of the Community would find it beneficial!!