Conditional formating based on a dynamic table

[Anonymous]

I created a dynamic table to use the option of a conditional formating over a matrix table….

In Excel is so easy (Excel is a crap) but here in PBI I have to do it over every column (which are measures %M1, %M2, %M3 and so on)

The idea is that the conditional formating considers the low number of my table the best (green)

anybody could give me a hand with some idea link where I could learn?

PBIX sample: https://www.dropbox.com/s/f724xn7fwm711lc/AGING.pbix?dl=0 

Really appreciate any help!!!!!!

My dinamic Table:

My desired result is what I have in excel:

[v-janeyg-msft]

Hi, @Anonymous 

It’s my pleasure to answer for you.

According to your description, I think you can use ‘format by rules’ to display.

Like this:

Here is my sample file for you. Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-janeyg-msft

Thank for helping me out

But I need to paint according to the dynamic table.

Any idea?

[v-janeyg-msft]

Hi, @Anonymous 

Can you show the desired result about the dynamic table, I'm not sure what you really need.

Best Regards

Janey Guo

[Anonymous]

@v-janeyg-msft Conditional formating needs to be based on max and min (Did you get the idea?)

[v-janeyg-msft]

Hi, @Anonymous 

It may be a little difficult to format based on dynamic.

You need to create some measures with %M1-%M12 ,then use conditional format.

Try like this:

%M1color =
VAR V =
    SUM ( DetalleFacturado[M1] ) / SUM ( DetalleFacturado[ImporteLinea] )
RETURN
    IF (
        v <> BLANK (),
        IF ( V < 'MAX-MIN'[MAX] && V > 'MAX-MIN'[MIN], "Green", "Red" )
    )

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Not really...as you see shows the other colours green, they should be still red

[v-janeyg-msft]

Hi, @Anonymous 

I'm sorry that It is difficult to simultaneously use the maximum and minimum values in the dynamic table as the color format to present gradient colors in multiple measures.

This is a workaround.It can only display  specific colors within the range, you need to set it yourself according to your needs, as I wrote above.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

IT DOESNT. Ill wait a little more if someone else could help me out.

Thanks anyways.

[Anonymous]

it is what you did, but you added the numbers manually...I need the table (with colours) to consider the max values and min value out of that dynamic table automatically

[amitchandak]

@Anonymous , you should color scale with diverging option

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting#format-background-or-font-color

[Anonymous]

It doesnt work...everything is red or everything is green...why...I cant not see what am I doing wrong...can you give me a hand? @amitchandak