cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
drawser
Helper I
Helper I

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

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
            )
        )
    )
)

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

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
    )
)

View solution in original post

6 REPLIES 6
GarethPrisk
Super User II
Super User II

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
Super User III
Super User III

@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.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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

 

drawser_0-1600698681839.png

 

And this happens when it's set to show 2020

drawser_1-1600698811143.png

 

 

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?

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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
    )
)

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,097)