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

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é

  • 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

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

    afbraga66 

     

    The data is not important, it is because the context changes because of what you changed. How do we judge if you don’t say it? You should know that the result of the measure is very flexible.

    Try: change 'all' to 'allselected' in the measure.

     

    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,

    Community Support Team _ Janey

     

     

  • afbraga66's avatar
    afbraga66
    Helper III
    5 years ago

    Hello v-janeyg-msft ,

     

    Ok, so the measure and fields in the visual are exactly the same as my example.

    There are some differences in other things indeed.

    I have these filters on visual and on page.

    Also have a slicer field on the page named NOME_AREA, which affects the table visual.

     

    Thank you.

    Best regards,

    André

     

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

    Hi, afbraga66 

     

    Have you tried the advice I gave you before(all to allselected)? The problem may be here. 'filter on this page' will filter some rows, which contain data that you might meet the conditional format. You can delete it to see if it is displayed correctly. 

    You need to add the conditions in the maximum and minimum places. 

    Like this:

    I will be on vacation for a while,so can't reply in time. Hope you can understand what I mean.

     

    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,

    Community Support Team _ Janey

     

  • afbraga66's avatar
    afbraga66
    Helper III
    5 years ago

    Hi v-janeyg-msft ,

     

    I understand, have a nice time on your vacations.

    Just on a side note, I tried the allselected but was the same. Additionally the MAXX is giving syntax erro because it only takes 2 parameters, and in your example you are adding a third.

    Thank you.

     

    Best regards,

    André

  • mussaenda's avatar
    mussaenda
    Community Champion
    5 years ago

    Hi afbraga66 ,

     

    v-janeyg-msft 's advice to change all to allselected worked on your test file. 

    I even added a filter on the filter pane and still working correctly.

     

  • afbraga66's avatar
    afbraga66
    Helper III
    5 years ago

    Hi mussaenda ,

     

    The image below has the measure with ALLSELECTED, with 2 filters. One on visual and other on the page. As you can see it is not working. This is from the test file.

     

     

    Thank you.

    Best regards,

    André

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

    Hi, afbraga66 

     

    You should know that the measure in powerbi is very flexible, especially when you want to calculate dynamic values instead of fixed values. A little difference in context may cause different results.
    If you can't provide some complete sample files or modify them according to my code, it is difficult for us to really help you.

    If you have any questions about my code, please ask questions.

    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
    4 years ago

    Hello v-janeyg-msft 

     

    Hope you had a good rest 🙂

     

    So I set up a test file with the formula you provided, but still I wasn't able to get always the same result based on filters change. I also tried the conditions in the MAXX and MINX, but I get syntax error.

    File below:

    https://we.tl/t-Q0v92xcjN1

     

    I have not understood so far the proper way to deal with filters when creating a formula, since it gives different results if I change filters. I expected always the same result, the higher value painted in red, the lower in green, the middle in something else.

     

    Thank you.

     

    Best regards,

    André

     

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

    Hi, afbraga66 

     

    Try this:

    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 SWITCH(TRUE(),[mDuration(min)]=a,"Red",[mDuration(min)]=b,"Green","Orange")

    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
    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é