Forum Discussion

afbraga66's avatar
afbraga66
Helper III
5 years ago
Solved

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...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    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

amitchandak's avatar
amitchandak
Super User
5 years ago

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

 

afbraga66's avatar
afbraga66
Helper III
5 years ago

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é

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    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