Forum Discussion
Color matrix by column total
Hello,
The images below are the same matrix that have a formula, that changes dinamically upon the selection of a button - absolute or percentage. I am trying to find a way to do the color of the background by column instead of all the values in the matrix.
Ex: In column 7 - 38,04 should be colored in red, as it is the highest value.
Anyway this can be done?
This is the color formatting options.
Thank you.
Best regards,
André
Hi, afbraga66
You can try:
Color Measure = var c =SUMMARIZE(ALLSELECTED(DOWNTIME),DOWNTIME[NOME_TIPO],DOWNTIME[MES_FABRIL],"sum",SUM(DOWNTIME[Duration Minutes])) var a =MAXX(FILTER(c,[MES_FABRIL]=SELECTEDVALUE(DOWNTIME[MES_FABRIL])),[sum]) var b =MINX(FILTER(c,[MES_FABRIL]=SELECTEDVALUE(DOWNTIME[MES_FABRIL])),[sum]) return IF(ISINSCOPE(DOWNTIME[NOME_TIPO])&&NOT(ISINSCOPE(DOWNTIME[COD_OP])),SWITCH(TRUE(),[mDuration(min)]=a,"Red",[mDuration(min)]=b,"Green","Orange"))I use summraize funtion to generate a virtual table with the same values like your matrix visual then use it to calculate.
If you use expand or collapse, you can use isinscope funtion or isfilter funtion to calculate the results of the main and hierarchical levels separately.
There is no one-time formula. When you use matrix visualization and have multiple row fields, the context becomes more complicated, and the code needs to be modified as the logic changes.
SUMMARIZE function (DAX) - DAX | Microsoft Docs
ISINSCOPE function (DAX) - DAX | Microsoft Docs
Did I answer your question ? Please mark my reply as solution. Thank you very much.
Best Regards,Community Support Team _ Janey
21 Replies
afbraga66 , You have to create color measure
color =
var _max = maxx(allselected(Table), Table[Colum] =max(Table[Column]),[Measure])
var _min = maxx(allselected(Table), Table[Colum] =max(Table[Column]),[Measure])
return
Switch ( True() ,
[Meausre] = _min ="Green",
[Meausre] = _max ="Red",
"Yellow"
)
Usie this measure in conditional formatting using field value option
refer if needed
How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4
Hey,
Thanks for your help.
I have tried to adapt your code but I am having issues (error below).
MES_FABRIL is the month column on the matrix which has the values 5,6,7,etc.
Duration(min) is just a sum of a column.
Also you have maxx when var is _min, is it on purpose?
color = var _max = maxx(allselected(DOWNTIME), DOWNTIME[MES_FABRIL]=max(DOWNTIME[MES_FABRIL]),[mDuration(min)]) var _min = maxx(allselected(DOWNTIME), DOWNTIME[MES_FABRIL] =max(DOWNTIME[MES_FABRIL]),[mDuration(min)]) return Switch ( True() , [mDuration(min)] = _min ="Green", [mDuration(min)] = _max ="Red", "Yellow" )While trying to make it work, I also go the message when applying it saying.
Thank you,
Best regards,
André
Hello, afbraga66
Your needs can be easily achieved by customizing the conditional format, But you only provide a visual screenshot, we don’t know what your source data is(Which column to put in values etc.), so it’s difficult to work out a reasonable measure for you to use. Please share more details.
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.Best Regards
Janey Guo
Hey v-janeyg-msft ,
I understand of course. Here is an app which can be used as an example.
In the image below the color map should by column - so 2026 -red, 657 - red and the same logic for rest of the values. The color should be set on a column value by column value evaluation, instead of all values in the table.
The columns 5 ,6 and 7 are values of a single column which represents the month.
Best regards,
André
afbraga66 Can you share a sample fake data file?
v-janeyg-msftwas in the post above. It is this one - https://we.tl/t-XlZUWPgAKL. The message ui won't let me add one in another way, so I add to put it on a link.
