I have a dataset named Heatmap which is in the shape/structure shared below:
| Cluster | Region | Country | Industry | Sector | % CAGR |
| EMEA | Middle East | Qatar | | | |
| EMEA | Middle East | Oman | | | |
| EMEA | Middle East | UAE | Consumer | Personal Care | 12% |
| EMEA | Middle East | UAE | Industrial | Automotive | 5% |
| EMEA | Middle East | UAE | Health | Pharma | 6% |
| APA | China/HK | China | Telecom | Media | 10% |
| APA | China/HK | HK | | | |
| APA | US | US | Consumer | Personal Care | 13% |
| APA | US | US | Industrial | Automotive | 6% |
| APA | US | US | Health | Pharma | 16% |
| APA | US | US | Telecom | Media | 30% |
| APA | UK | UK | Consumer | Personal Care | 2% |
| APA | UK | UK | Industrial | Automotive | 65% |
| APA | UK | UK | Health | Pharma | 11% |
| APA | UK | UK | Telecom | Media | 10% |
(and so on)
My objective is to plot the Region column in a filled map visual, wherein:
1. The colour for Middle East should include, for example, Qatar, Oman and UAE. The colour for China should include China and HK. I have several such 'groups' of countries, where one country serves as a proxy for the concerned region.
2. The colours will be conditioned on the % CAGR column i.e. the higher the figure, the darker the colour and vice-versa.
3. Filters will include: Cluster, Region, Industry and Sector. When any of these filters are used, the map should continue to represent the full region, using the info of the proxy countries.
4. Average of % CAGR is taken at the region level.
I've gotten as far as creating a conditional column:
2019-2022 CAGR (grouped) = var AvgME = CALCULATE(AVERAGE(Heatmap[2019-2022 CAGR]),FILTER(Heatmap,Heatmap[SCM region]="United Arab Emirates")) var AvgCNHK = CALCULATE(AVERAGE(Heatmap[2019-2022 CAGR]),FILTER(Heatmap,Heatmap[SCM region]="China")) return IF((Heatmap[Territory]="Middle East"&&Heatmap[Industry]=""),AvgME, (IF((Heatmap[Territory]="China/HK"&&Heatmap[Industry]=""),AvgCNHK, Heatmap[2019-2022 CAGR]))))))) |
And a measure for the map color to support the conditional formatting:
Map Color = var AvgCAGR = CALCULATE(AVERAGE(Heatmap[2019-2022 CAGR (grouped)])) return IF(AvgCAGR>=0.10,"#175d2d", IF(AND(AvgCAGR<0.10,AvgCAGR>=0.05),"#ffb600", IF(AvgCAGR<0.05,"#e0301e","#7d7d7d"))) |
But the 'groups' of countries are not formatted the same colour despite this - does anyone have any idea what I'm doing wrong?
