Forum Discussion

tasmiaa's avatar
tasmiaa
Advocate I
7 years ago
Solved

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!

Need Help
  • tasmiaa's avatar
    tasmiaa
    7 years ago

    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

    • tasmiaa's avatar
      tasmiaa
      Advocate I
      7 years ago

      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! :)

  • tasmiaa's avatar
    tasmiaa
    Advocate I
    7 years ago

    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