Treemap color with objectives

[adiolka]

Hello,

I hope that there werent similar post about my problem - i havent found anything similar.

I have questions about treemap color with objectives.

In my project i have result and i want add aim/objectives

At this moment in my projects i have just result, The highest score is in red color, middle score orange and the lowest one is green

(20%red)(7% green)

I want add the aim/objectives to this results. 
I would like the diagram not to show the highest result as red as above. Treemap should relate to the result based on the set goal. So for example if the result reached the goal then color will be green, if it is close to the goal it will be orange, if it is far away from the result it could be red.

I found in my company other projects with this function but i cant download it ( or i dont know how ) but the person which make it, dont work already in my company. 

I want to set my goals and aim as on the picture above.

Maybe somebody can help me with my problem, i must do something in main treemap or download some plugin ? 

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, I create a table to test:

Please take following steps:

1)Create a measure to set conditional format color:

Measure = 
SWITCH (
    TRUE,
    SELECTEDVALUE ( 'Table'[Scores] ) < 50 * 0.9, "#800000",
    SELECTEDVALUE ( 'Table'[Scores] ) > 50 * 0.9
        && SELECTEDVALUE ( 'Table'[Scores] ) < 50 * 0.92, "#B50000",
    SELECTEDVALUE ( 'Table'[Scores] ) > 50 * 0.92
        && SELECTEDVALUE ( 'Table'[Scores] ) < 50 * 0.94, "#FF0000",
    SELECTEDVALUE ( 'Table'[Scores] ) > 50 * 0.94
        && SELECTEDVALUE ( 'Table'[Scores] ) < 50 * 0.96, "#B55B00",
    SELECTEDVALUE ( 'Table'[Scores] ) > 50 * 0.96
        && SELECTEDVALUE ( 'Table'[Scores] ) < 50 * 0.98, "#FFC300",
    SELECTEDVALUE ( 'Table'[Scores] ) > 50 * 0.98
        && SELECTEDVALUE ( 'Table'[Scores] ) < 50 * 1, "#FFFF00",
    SELECTEDVALUE ( 'Table'[Scores] ) > 50 * 1
        && SELECTEDVALUE ( 'Table'[Scores] ) < 50 * 1.02, "#00FF00",
    SELECTEDVALUE ( 'Table'[Scores] ) > 50 * 1.02
        && SELECTEDVALUE ( 'Table'[Scores] ) < 50 * 1.04, "#00D100",
    SELECTEDVALUE ( 'Table'[Scores] ) > 50 * 1.04
        && SELECTEDVALUE ( 'Table'[Scores] ) < 50 * 1.06, "#00A000",
    SELECTEDVALUE ( 'Table'[Scores] ) > 50 * 1.06
        && SELECTEDVALUE ( 'Table'[Scores] ) < 50 * 1.08, "#006900",
    SELECTEDVALUE ( 'Table'[Scores] ) > 50 * 1.08
        && SELECTEDVALUE ( 'Table'[Scores] ) < 50 * 1.1, "#004500",
    SELECTEDVALUE ( 'Table'[Scores] ) > 50 * 1.1, "#004000"
)

And it shows:

2)Create a calculated table:

Standard = ADDCOLUMNS(GENERATESERIES(-0.1,0.12,0.02),"Level",[Value]*0+1)

Then, add a column to this table:

Column = ROUND(Standard[Value],2)

3)Create a measure to set this calculated table's conditional format color:

Measure 2 = 
SWITCH (
    TRUE,
    SELECTEDVALUE ( Standard[Column] ) = -0.1, "#800000",
    SELECTEDVALUE ( Standard[Column] ) = -0.08, "#B50000",
    SELECTEDVALUE ( Standard[Column] ) = -0.06, "#FF0000",
    SELECTEDVALUE ( Standard[Column] ) = -0.04, "#B55B00",
    SELECTEDVALUE ( Standard[Column] ) = -0.02, "#FFC300",
    SELECTEDVALUE ( Standard[Column] ) = 0, "#FFFF00",
    SELECTEDVALUE ( Standard[Column] ) = 0.02, "#00FF00",
    SELECTEDVALUE ( Standard[Column] ) = 0.04, "#00D100",
    SELECTEDVALUE ( Standard[Column] ) = 0.06, "#00A000",
    SELECTEDVALUE ( Standard[Column] ) = 0.08, "#006900",
    SELECTEDVALUE ( Standard[Column] ) = 0.1, "#004500"
)

4)Choose a treemap visual:

And it shows:

Here is my test pbix file:

pbix 

Best Regards,

Giotto Zhi

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, I create a table to test:

Please take following steps:

1)Create a measure to set conditional format color:

Measure = 
SWITCH (
    TRUE,
    SELECTEDVALUE ( 'Table'[Scores] ) < 50 * 0.9, "#800000",
    SELECTEDVALUE ( 'Table'[Scores] ) > 50 * 0.9
        && SELECTEDVALUE ( 'Table'[Scores] ) < 50 * 0.92, "#B50000",
    SELECTEDVALUE ( 'Table'[Scores] ) > 50 * 0.92
        && SELECTEDVALUE ( 'Table'[Scores] ) < 50 * 0.94, "#FF0000",
    SELECTEDVALUE ( 'Table'[Scores] ) > 50 * 0.94
        && SELECTEDVALUE ( 'Table'[Scores] ) < 50 * 0.96, "#B55B00",
    SELECTEDVALUE ( 'Table'[Scores] ) > 50 * 0.96
        && SELECTEDVALUE ( 'Table'[Scores] ) < 50 * 0.98, "#FFC300",
    SELECTEDVALUE ( 'Table'[Scores] ) > 50 * 0.98
        && SELECTEDVALUE ( 'Table'[Scores] ) < 50 * 1, "#FFFF00",
    SELECTEDVALUE ( 'Table'[Scores] ) > 50 * 1
        && SELECTEDVALUE ( 'Table'[Scores] ) < 50 * 1.02, "#00FF00",
    SELECTEDVALUE ( 'Table'[Scores] ) > 50 * 1.02
        && SELECTEDVALUE ( 'Table'[Scores] ) < 50 * 1.04, "#00D100",
    SELECTEDVALUE ( 'Table'[Scores] ) > 50 * 1.04
        && SELECTEDVALUE ( 'Table'[Scores] ) < 50 * 1.06, "#00A000",
    SELECTEDVALUE ( 'Table'[Scores] ) > 50 * 1.06
        && SELECTEDVALUE ( 'Table'[Scores] ) < 50 * 1.08, "#006900",
    SELECTEDVALUE ( 'Table'[Scores] ) > 50 * 1.08
        && SELECTEDVALUE ( 'Table'[Scores] ) < 50 * 1.1, "#004500",
    SELECTEDVALUE ( 'Table'[Scores] ) > 50 * 1.1, "#004000"
)

And it shows:

2)Create a calculated table:

Standard = ADDCOLUMNS(GENERATESERIES(-0.1,0.12,0.02),"Level",[Value]*0+1)

Then, add a column to this table:

Column = ROUND(Standard[Value],2)

3)Create a measure to set this calculated table's conditional format color:

Measure 2 = 
SWITCH (
    TRUE,
    SELECTEDVALUE ( Standard[Column] ) = -0.1, "#800000",
    SELECTEDVALUE ( Standard[Column] ) = -0.08, "#B50000",
    SELECTEDVALUE ( Standard[Column] ) = -0.06, "#FF0000",
    SELECTEDVALUE ( Standard[Column] ) = -0.04, "#B55B00",
    SELECTEDVALUE ( Standard[Column] ) = -0.02, "#FFC300",
    SELECTEDVALUE ( Standard[Column] ) = 0, "#FFFF00",
    SELECTEDVALUE ( Standard[Column] ) = 0.02, "#00FF00",
    SELECTEDVALUE ( Standard[Column] ) = 0.04, "#00D100",
    SELECTEDVALUE ( Standard[Column] ) = 0.06, "#00A000",
    SELECTEDVALUE ( Standard[Column] ) = 0.08, "#006900",
    SELECTEDVALUE ( Standard[Column] ) = 0.1, "#004500"
)

4)Choose a treemap visual:

And it shows:

Here is my test pbix file:

pbix 

Best Regards,

Giotto Zhi

[adiolka · Accepted solution]

@v-gizhi-msft 

Thank u so much i really appreciate your help 🙂 
I did it. 

For everyone which have problem like mine, must folow like on the picture below

[amitchandak]

I tried color Sales heat map by Margin Using Advance Control in Data color. If this can help

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[adiolka]

@amitchandak thanks for your answer but i used it to add a color to try if the diagram works properly.

Now I add a aim/objectives and i need set the color for a results which i connected with aim/objectives.

[amitchandak]

 If possible please share a sample pbix file after removing sensitive information. Bases on that data let us know what color you want.

I used a measure. So I have different. Option. You can even create a Measure and return colors based on condition.  And in advance control, you can use that field value.

eaxmple

Color Column = if('Date'[date]<TODAY(),"green","red") 
Color Measure = if(FIRSTNONBLANK('Date'[date],TODAY()) <today(),"lightgreen","red")

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[adiolka]

Sure, i will share. I thinking about 

Mayby its additional widget ? Or if no, how can i create it ? 

[adiolka]

@amitchandak 

Already i almost do this.
I used if and switch 

But on this moment i have 2 colors, green and red. If i have interval from -...% to +...% which command i can use for create orange color ? For example if i have % on + ( 0-100% ) i have red color, if i have from 0% to Max -5% i have orange color, and if i have -...% i have green color ?