Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Spain provinces from zipcode

Hello,

 

I'm struggling to create filled map with sales by departments in different countries.

In both France and Spain the first two digits of the postal code denote the corresponding department.

So, I created the column
Department = LEFT(Customers[ZIPCODE],2)
I create a filled map visual and put [Department] in the "Location", [Sales] in the tooltip and it works ... for France, but not for Spain.

Spanish departments are projected to the french ones.

So, the sales in the spanish department 19 (Guadalajara) are shown in the french department 19 (Corrèze).

Putting a slicer on the [Country] helps only in a sense that it removes spanish sales from the map of France when "France" is selected.

When "Spain" is selected in the slicer, it just projects spanish sales on the map of France.

 

I'd ve very grateful for any suggestion how to solve this problem.

 

Here is a small data sample:

'Customers' table:

ACCOUNTNUMCITYZIPCODECountryDepartment
401000FUENTERROBLES46314ESP46
402644BARCELONA08038ESP08
402645Constanti43120ESP43
403006BARCELONA08038ESP08
400999REQUENA46340ESP46
401001BENIFARAIG46016ESP46
402683BARCELONA08041ESP08

 

'Sales' table:

Current salesORDERACCOUNTYear invoiced
1834026442023
2114026442023
5924026442023
7084026452022
6444026452023
4834026452023
9664026452023
2004030062023
1134030062023
504030062023

There is 'one-to-many' relationship between 'Customers'[ACCOUNTNUM] and 'Sales'[ORDERACCOUNT].

1 Reply