Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
v-cherch-msft
Employee
Employee

 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.

View solution in original post

8 REPLIES 8
brushben
New Member

Very Interesting and wonderful information keep sharing this post kindly check

railway information time table
 



v-cherch-msft
Employee
Employee

 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

Thanks @v-cherch-msft

 

After using the allexcept in the correct column all worked.

 

Thanks

Anonymous
Not applicable

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.

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

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

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

Hello @v-cherch-msft

 

Thanks for the quick reply. 

 

It is almost working.. 🙂

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.