cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Conditional formatting on table based on cell value till the cell

Hello to all

 

I don't know if this is possible and i'm not beeing able to do it.

 

Consider the following table:

Name    Value

cell1      10

cell2      15

cell3      0

cell4      20

cell5      5

cell6      8

cell7      7

 

MeasureA with value 35.

 

What I would like to do is something like this:

cell1 Value is less that MeasureA, yes.. color it green

cell1 Value + cell2 Value is less that MeasureA, yes color it green

cell1 Value + cell2 Value + cell3 Value is less that MeasureA, yes color it green

cell1 Value + cell2 Value + cell3 Value + cell4 Value is less that MeasureA, no don't color it..

 

Is this achievable?

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Conditional formatting on table based on cell value till the cell

 Hi @Anonymous

 

You may try to add some measure and then use conditional formatting on the table.

MeasureB =
CALCULATE (
    SUM ( Table4[Value] ),
    FILTER ( ALL ( Table4 ), Table4[Name ] <= MAX ( Table4[Name ] ) )
)
MeasureC =
IF ( [MeasureB] < [MeasureA], 1 )

1.png

 

Regards,

Cherie

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 REPLIES 8
Community Support Team
Community Support Team

Re: Conditional formatting on table based on cell value till the cell

 Hi @Anonymous

 

You may try to add some measure and then use conditional formatting on the table.

MeasureB =
CALCULATE (
    SUM ( Table4[Value] ),
    FILTER ( ALL ( Table4 ), Table4[Name ] <= MAX ( Table4[Name ] ) )
)
MeasureC =
IF ( [MeasureB] < [MeasureA], 1 )

1.png

 

Regards,

Cherie

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Conditional formatting on table based on cell value till the cell

Hello @v-cherch-msft

 

Thanks for the quick reply. 

 

It is almost working.. Smiley Happy

 

It gives me a intial value (in my case 15) which in fact should be 0. It might be due to filters I have. I tried whitout the all but the result is the same.

 

How can I overcome this?

 

Regards

Anonymous
Not applicable

Re: Conditional formatting on table based on cell value till the cell

Hello again @v-cherch-msft

 

In fact what I have is:

ID    Order    Value

3         3          10

4         6          4

2         7          0

1         9          6

5         10        0

8         11        0

6         15        3

7         16        7

 

With this measure as you suggest:

MeasureB =
CALCULATE (
SUM ( Table4[Value] );
FILTER ( ALL( Table4 ) ; Table4[Order] <= MAX ( Table4[Order] ) )
)

 

but if i add the measure to the table I get incorrect values. If I remove ALL it is even worse because it does not add as expected.

Community Support Team
Community Support Team

Re: Conditional formatting on table based on cell value till the cell

Hi @Anonymous

 

It seems nothing wrong with your measure. You need drag the order column and value column in table visual.

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Conditional formatting on table based on cell value till the cell

Hello @v-cherch-msft

 

The problem, in my case, is that I have the table filter.. and in the example given. order 1 and 2 for example are filtered but also have a Value, so what is dispalyed in the Measure is not 10 but in my case 25..

 

I think this is due to the ALL part in the filter.

 

Is this the case?

 

Thanks

brushben Occasional Visitor
Occasional Visitor

Re: Conditional formatting on table based on cell value till the cell

Very Interesting and wonderful information keep sharing this post kindly check

railway information time table
 



Community Support Team
Community Support Team

Re: Conditional formatting on table based on cell value till the cell

Hi @Anonymous

 

You may try to use ALLSELECTED Function instead of All function.

 

Regards,
Cherie 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Conditional formatting on table based on cell value till the cell

Thanks @v-cherch-msft

 

After using the allexcept in the correct column all worked.

 

Thanks