Forum Discussion
Power BI Map - Sales Data
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 check this link out: https://docs.microsoft.com/en-us/power-bi/visuals/desktop-shape-map
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 you may need to convert to JSON: https://www.statsilk.com/maps/convert-esri-shapefile-map-geojso
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
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
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.
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
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
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 🙂