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

(0) Share

Report

Posted on by drawser

121

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

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

GarethPrisk 2,828 on at

Like (0)

Report
Copy link

Link copied!

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-example-change-the/td-p/186171

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@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.

Was this reply helpful? Yes No
drawser 121 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
drawser 121 on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Verified answer

drawser 121 on at

Like (0)

Report
Copy link

Link copied!
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 ) )

Was this reply helpful? Yes No