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:
| ACCOUNTNUM | CITY | ZIPCODE | Country | Department |
| 401000 | FUENTERROBLES | 46314 | ESP | 46 |
| 402644 | BARCELONA | 08038 | ESP | 08 |
| 402645 | Constanti | 43120 | ESP | 43 |
| 403006 | BARCELONA | 08038 | ESP | 08 |
| 400999 | REQUENA | 46340 | ESP | 46 |
| 401001 | BENIFARAIG | 46016 | ESP | 46 |
| 402683 | BARCELONA | 08041 | ESP | 08 |
'Sales' table:
| Current sales | ORDERACCOUNT | Year invoiced |
| 183 | 402644 | 2023 |
| 211 | 402644 | 2023 |
| 592 | 402644 | 2023 |
| 708 | 402645 | 2022 |
| 644 | 402645 | 2023 |
| 483 | 402645 | 2023 |
| 966 | 402645 | 2023 |
| 200 | 403006 | 2023 |
| 113 | 403006 | 2023 |
| 50 | 403006 | 2023 |
There is 'one-to-many' relationship between 'Customers'[ACCOUNTNUM] and 'Sales'[ORDERACCOUNT].
