cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## How to color a filled map dynamically

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Regular Visitor

## Re: How to color a filled map dynamically

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]),
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

3 REPLIES 3
Community Support Team

## Re: How to color a filled map dynamically

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: How to color a filled map dynamically

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!

Regular Visitor

## Re: How to color a filled map dynamically

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]),