Color change based on a number

[Mike_Driscoll]

Is there a formula that would make positive number Green and the same number Red if it goes negative? I’m tracking the change in events between the two previous weeks. Here’s the current formula which does work.
“%” + Round((

[Event Master Log].Filter(Week = WeekNumber(Today()) - 1).Count()

• [Event Master Log].Filter(Week = WeekNumber(Today()) - 2).Count()
  ) / If([Event Master Log].Filter(Week = WeekNumber(Today()) - 2).Count() = 0, 1, [Event Master Log].Filter(Week = WeekNumber(Today()) - 2).Count()) * 100,2)

[Pch]

Hi @Mike_Driscoll !

If your formula is on the canvas, you could use Format() and while storing the needed templates “elsewhere” (in a table, for example) apply the desired color on the placeholder Format() uses to do what it needs to do which should then be applied on the value when Format() runs .

Here’s a very very very quick sample (down below ) where I’ve created 3 templates Format() should use depending on the value of the canvas slider
All the rows containing the needed templates have been @ref (e.g.: @Negative.Template)

The canvas formula looks like this :

Format(
  // Template to use depending on the value of [slider 1]
  SwitchIf(
    [slider 1] < 0,
    Negative.Template,
    [slider 1] = 0,
    Zero.Template,
    [slider 1] > 0,
    Postive.Template
  ),
  // the value the placeholder {1} should take
  [slider 1]
)

And I guess your formula should replace the [slider 1] I used

To avoid having to recalculate your Round() formula too many times, you could store its value using WithName() so the formula should become something like:

// Store the value returned by Round( ... ) within RoundOutput
Round( ... ).WithName(RoundOutput,
    Format(
      // Template to use depending on the value of RoundOutput
      SwitchIf(
        RoundOutput < 0,
        Negative.Template,
        RoundOutput = 0,
        Zero.Template,
        RoundOutput > 0,
        Positive.Template
      ),
    // the value the placeholder {1} should take
    RoundOutput
  )
)

Now, if it’s in table, you could apply some conditional formatting instead …

I hope this helps a little