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

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

Hi, afbraga66 

 

Try like this:

 

Measure = 
var a =MAXX(ALL(DOWNTIME[NOME_TIPO]),[mDuration(min)])
var b =MINX(ALL(DOWNTIME[NOME_TIPO]),[mDuration(min)])
return SWITCH(TRUE(),[mDuration(min)]=a,"Red",[mDuration(min)]=b,"Green","Yellow")

 

 

 

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.


Best Regards,

Community Support Team _ Janey

  • afbraga66's avatar
    afbraga66
    Helper III
    5 years ago

    Hey v-janeyg-msft ,

     

    It does work on the test file.

    I tried to apply it to my report and this is the result. Overall is okay, not sure why there is no red color in the 7th month column  for the 23,74 value. Any idea what could be causing it?

    Best regards,

    André

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

      afbraga66 

       

      The context is different, the result will be different, I don’t know what the data and context in your source file are like.  What's the measure in value?

       

      Best Regards,

      Community Support Team _ Janey

      • afbraga66's avatar
        afbraga66
        Helper III
        5 years ago

        Hey v-janeyg-msft ,

         

        I understand what you mean. The data is pretty much the same just less column fields for the table in the model, but the fields that are considered to make the key unique are already there. In the end it also has less rows just because I deleted the rest. That's the reason for my confusion on why it is not working.

        Is there any way I can export the complete model table to an excel or csv to create a new file based on it?

         

        Thanks

        André