Color matrix by column total

[afbraga66]

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é

[afbraga66]

Hey @v-janeyg-msft ,

I understand of course. Here is an app which can be used as an example.

https://we.tl/t-XlZUWPgAKL

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é

[v-janeyg-msft]

@afbraga66  Can you share a sample fake data file?

[afbraga66]

@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.

[v-janeyg-msft]

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]

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]

@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]

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]

@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]

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]

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

[mussaenda]

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]

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]

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]

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]

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]

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 · Accepted solution]

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]

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é

[amitchandak]

@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]

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é