Why isn't this formatting working in my Item Color Set

[drawser]

Hi,

I have a column chart that displays values per month

The values themselves range from 0 to 3

I also have a radio control that allows to select year with the default year set to 2020

Now, I want to format the color of the bars based on the values, 0 - 1.99 = Red, 2 - 2.99 = Orange, 3 = Green

I sort of have it do what I want for each year except for the default selected, 2020. Then it starts formatting the value 3 as red

My Item Color Set it set to this, what am I not getting right in this?

I'm using a For All to format based on the value

ForAll(
    Filter(
        ReportHead,
        Adress.Id = Gallery2_9.Selected.ID,
        RadioYear.SelectedText.Value in Text(
            Date,
            "[$-en-US]yyyy"
        )
    ),
    If(
        Value(AvgValue) >= 3,
        Color.LightGreen,
        If(
            Value(AvgValue) >= 2 && Value(AvgValue) < 3,
            Color.Orange,
            If(
                Value(AvgValue) >= 0 && Value(AvgValue) < 2,
                Color.Red
            )
        )
    )
)

[GarethPrisk]

The Color Set is an array of colors, which are then mapped to the series for the chart.

Your formula may produce an indefinite number of results (since it's a filtered record set, and will also change if the filter criteria is updated).

Are you simply trying to set [Red,Orange,Green]?

The chart itself (if structured correctly), should be correlating the series to the colors.

Maybe this will help? https://powerusers.microsoft.com/t5/Building-Power-Apps/How-to-change-the-column-s-chart-s-color-For...

[RandyHayes]

@drawser 

In general, the formula is fairly accurate.

I would only modify to this and with a few questions:

ForAll(
    Filter(
        ReportHead,
        Adress.Id = Gallery2_9.Selected.ID,
        RadioYear.SelectedText.Value = Text(Date, "[$-en-US]yyyy")
    ),
    If(
        Value(AvgValue) >= 3, Color.LightGreen,
        Value(AvgValue) >= 2, Color.Orange,
        Color.Red
    )
)

The question is: Is AvgValue a Text column?  If not, then remove the Value function in the If statement.

I hope this is helpful for you.

[drawser]

Thanks, I updated to your formula, it still however does not produce the desired result.

The reason I had value in there was that I wanted to make sure it was reading actual values.

What strikes me as odd is, if I change the radio control to another year than the default, it seems to work as intended.

This is for example for 2018

And this happens when it's set to show 2020

[drawser]

I'm trying to set the color based on the column value that is series 1,

 Doesnt doing this [Green, Orange, Red] change the color based on the Labels instead?

[RandyHayes]

@drawser 

Ah, I see from the picture the probably cause.  As @GarethPrisk mentioned, the ColorSet is an array that maps to the series.

Your series is missing columns.

Your color set needs to have the exact same amount of rows that your items results would produce.

EDIT: So to add.  Your Items property filter should be identical to the color set filter.

[drawser · Accepted solution]

I added a column to my collection ReportHead and set it to be text based on the value, Value  >=  3, "Approved", Value >= 2 && Value <3, "Not quite there", Value >=0 && Value < 2, "Not approved".  And then use the texts in a switch statement instead.

So far it seems to be working as I want it to.

ForAll(Filter(
            ReportHead,
            Adress.Id = Gallery2_9.Selected.ID,
            RadioYear.SelectedText.Value in Text(
                Date,
                "[$-en-US]yyyy"),
    Switch(
        Outcome,
        "Approved",
        Color.LightGreen,
        "Not Quite There",
        Color.Orange,
        "Not approved",
        Color.Red
    )
)