Forum Discussion
Treemap color with objectives
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 ?
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:
Best Regards,
Giotto Zhi
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
7 Replies
- v-gizhi-msft
Community Support
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:
Best Regards,
Giotto Zhi
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
Super User
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-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601amitchandak 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-trend/ba-p/882970
