How to color a filled map dynamically

[tasmiaa]

I have the following data:

Table with countries, date, and a value

Country	Value	Year
USA	3	2016
France	6	2016
Germany	2	2016
USA	1	2017
France	4	2017
Germany	5	2017

Measures:

Avg_Val_CurrentYear = Calculate(Average(Value), DATEADD('Date'[Date], 0, Year))   --I'm sure there was a better way

Avg_Val_PreviousYear = Calculate(Average(Value), DATEADD('Date'[Date], -1, Year))

My goal: 

Create a map where the country is red if the Avg_Val_CurrentYear is less than Avg_Val_PreviousYear and green if Avg_Val_CurrentYear is greater than Avg_Val_PreviousYear. The page will have a slicer to select the year so this would change based on the year I have selected. 

How can I do this? I've tried creating a calculated column in the table that marks it red or green based on the 2 measures, but it does not mark the colors properly. Any advice would be appreciated.

Thanks!

[tasmiaa · Accepted solution]

I found the solution:

1. Keep measure Avg_Val_CurrentYear
2. Create a new table-
   Table = SUMMARIZE(NATURALINNERJOIN('Table', 'Date'),'Table'[Country],'Date'[Date], "Value", [Avg_Val_CurrentYear] )
3. Create a measure on the table for previous year
   
   Avg_Val_PreviousYear = CALCULATE (
   AVERAGE(Value),
   ALLEXCEPT ( 'Table', 'Table'[Country], 'Table'[Date]),
   DateADD('Table'[Date], -1, Year))
4. Create a calculated column on the table
   Color = If(Value < Avg_Val_PreviousYear, "red", "green")
5. Create a filled map with the country, value and color
   

[v-frfei-msft]

Hi @tasmiaa ,

Based on my test, it is not possible to conditional formatting in shape map in power bi.You can come up a new idea about that and add your comments there to improve Power BI and make this feature coming sooner.

https://ideas.powerbi.com/forums/265200-power-bi-ideas

Regards,

Frank

[tasmiaa]

Thanks, I was actually asking about a filled map and not a shape map, but I was able to figure out the issue. My solution is posted on the thread! 🙂