cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tasmiaa Regular Visitor
Regular Visitor

How to color a filled map dynamically

I have the following data:

 

Table with countries, date, and a value

CountryValueYear
USA32016
France62016
Germany22016
USA12017
France42017
Germany52017

 

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
tasmiaa Regular Visitor
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]),
    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
     
     
     
3 REPLIES 3
Community Support Team
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.
tasmiaa Regular Visitor
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! Smiley Happy

tasmiaa Regular Visitor
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]),
    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