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

afbraga66's avatar
afbraga66
Helper III
4 years ago

Hey v-janeyg-msft ,

 

This works indeed, thank you so much.

I have an additional doubt how do we deal when we have more than one row field in a pivot table?

 

I tried to add a new row field and it doesn't work anymore. I added the new field to the measure, but then it only works when the table is fully expanded. How can we make sure it works correctly in all different expand or collapse combinations?

Thank you.

 

I have added a file below.

https://we.tl/t-buuJ0l7ohZ

 

Best regards,

André

v-janeyg-msft's avatar
v-janeyg-msft
Community Support
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