Conditional formatting based on Comparison of 2 Calculated Fields

Question & Answer
, , , ,
1

Hi
want to have a conditional formatting based on the comparison of
dynamic values of a calculated measures (like in Power bi).
I mean, we can compare 2 measure & based on that we can give color coding.

like this

ifelse(

{Yesterday Scrap Value} < {Yesterday Scrap Value Target}, ‘Red Color’

{Yesterday Scrap Value} >= {Yesterday Scrap Value Target} AND {Yesterday Scrap Value} <={Yesterday Scrap Red Value}, Yellow Color

{Yesterday Scrap Value} > {Yesterday Scrap Red Value}, Green Color

)

The values of the above 2 Calculated Fields changes on daily basis. ( We can achieve this on Static Values but we can’t rely on static values )

I have achieved using static values given to a Calculated filed but i want to compare 2 Calculated Fields

Static Scenario:

image
image562×726 41.4 KB

Dynamic Scenario? Achievable in QS?

1 Like
2

Hi @Venkat.Metadata ,

Instead of encoding the colour name, we can encode the same condition with a value , lets say 1, 0 or -1 and then you use the same field to achieve conditional formatting.

e.g. -
ifelse({Yesterday Scrap Value} < {Yesterday Scrap Value Target}, -1,
{Yesterday Scrap Value} >= {Yesterday Scrap Value Target} AND {Yesterday Scrap Value} <={Yesterday Scrap Red Value}, 0,
{Yesterday Scrap Value} > {Yesterday Scrap Red Value}, 1)

Thanks,
Prantika

3

@prantika_sinha , the above formula is not working

Yesterday Scrap Cost = coalesce( ifelse( dateDiff({Date}, truncDate(‘DD’,now()),‘DD’)=1, {Value_decimal}, NULL), 0)

Yesterday Scrap Cost Target = coalesce( ifelse( dateDiff({Date}, truncDate(‘DD’,now()),‘DD’)=1, {Target}, NULL), 0 )

Yesterday Scrap Red Value = coalesce( ifelse( dateDiff({Date}, truncDate(‘DD’,now()),‘DD’)=1, {Red}, NULL), 0)

OR the below formula is not working

ifelse (
coalesce( ifelse( dateDiff({Date}, truncDate(‘DD’,now()),‘DD’)=1, {Value_decimal}, NULL), 0) <
coalesce( ifelse( dateDiff({Date}, truncDate(‘DD’,now()),‘DD’)=1, {Target}, NULL), 0 ), -1,
coalesce( ifelse( dateDiff({Date}, truncDate(‘DD’,now()),‘DD’)=1, {Value_decimal}, NULL), 0) >
coalesce( ifelse( dateDiff({Date}, truncDate(‘DD’,now()),‘DD’)=1, {Target}, NULL), 0 ) AND
coalesce( ifelse( dateDiff({Date}, truncDate(‘DD’,now()),‘DD’)=1, {Value_decimal}, NULL),0) <=
coalesce( ifelse( dateDiff({Date}, truncDate(‘DD’,now()),‘DD’)=1, {Red}, NULL), 0), 0,
coalesce( ifelse( dateDiff({Date}, truncDate(‘DD’,now()),‘DD’)=1, {Value_decimal}, NULL), 0) >
coalesce( ifelse( dateDiff({Date}, truncDate(‘DD’,now()),‘DD’)=1, {Red}, NULL), 0), 1
)

ifelse(
{Yesterday Scrap Value} < {Yesterday Scrap Value Target}, -1,
ifelse ( {Yesterday Scrap Value} >= {Yesterday Scrap Value Target} AND {Yesterday Scrap Value} =< {Yesterday Scrap Red Value}, 0,
ifelse( {Yesterday Scrap Value} > {Yesterday Scrap Red Value}, -1, null)
)
)

image
image982×110 4.04 KB

none formula is not working

4

Hi @Venkat.Metadata

Could you please try the following calculated field and apply the conditional formatting.

Example:

ifelse(
 {AttendanceOver30Days} < {AttendanceTarget30Days},      1,  // red
 {AttendanceOver30Days} <= {AttendanceWarnThreshold},    2,  // yellow
                                                           3   // green
)

Apply color formatting:

  1. Select your gauge visual → Format visual.

  2. Expand Conditional formatting → Arc → Add rule

  3. Set Format field based on → PerfCode and Aggregation → Custom (no aggregation)

    • Code = 1 → Red
    • Code = 2 → Yellow
    • Code = 3 → Green

  4. Click Apply

The above calculated field is itself driven by your two measures, the gauge arc will recolor automatically as those values change.

5

Hi @Venkat.Metadata

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.

Thank you!

6

I am not able to achieve with your information. Pls give more info.