Unanswered

Conditional formatting based on average or range like color scales in excel?

(0) Share

Report

Posted on by BrianHFASPS

718

I am trying to figure out if there is a way to do conditional formatting of a gallery similar to the way excel does it based upon average or range? I have a collection with a field that is a number from 0-20 for example and I want those on the higher end to be green, middle yellow and low end red. I don't know what the range is going to be ahead of time so I can't make a strict formula. Any suggestions?

Categories:

Building Power Apps

I have the same question (0)

All responses (15)

Answers (0)

WarrenBelz 153,084 Most Valuable Professional on at

Like (1)

Report
Hi @BrianHFASPS ,
Below is a gallery with the following in the TemplateFill property - will this do what you need?
Switch( ThisItem.DeviceType, "Laptop", Yellow, "All in one", GreenYellow, "Ultrabook", LightBlue, "Tablet", LightGreen, "Netbook", LightCoral )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
mdevaney 29,989 Moderator on at

Like (1)

Report

@WarrenBelz

I'm a bit of an Excel nerd so I thought I'd give offer a quick translation of what I think Brian is asking for.

In the image below there are several numbers. The lowest numbers are highlighted in red, the middle in yellow, the highest in green. I believe the colors are determined by percentiles though I don't exactly know the calculation.

Between the two of you I'm sure you'll come up with something eye pleasing.

Was this reply helpful? Yes No
WarrenBelz 153,084 Most Valuable Professional on at

Like (1)

Report
Thanks @mdevaney ,
@BrianHFASPS will need an If statement with ascending logic in the TemplateFill
If( Value(FieldName)<20, Color1, If( Value(FieldName)<40, Color2, If( Value(FieldName)<60, Color3, If( Value(FieldName)<80, Color4, Color5 ) ) ) )

Was this reply helpful? Yes No
BrianHFASPS 718 on at

Like (0)

Report

Thanks both of you but the problem is I don't know the range in advance and as time goes on it will increase. I need it to calculate an average for values in a collection and then say above and below that average. Maybe average isn't the best as much as largest being top and lowest being bottom with the range being the difference.

Maybe I find the top and bottom value, find the difference then divide by 3 or 5 and use that as scale. Let me think about the math on it.

I can use your code and then add my math maybe.

Was this reply helpful? Yes No
WarrenBelz 153,084 Most Valuable Professional on at

Like (3)

Report
Hi @BrianHFASPS ,
With a bit of manipulation, you can do this - the following will show what percentage the current record is of the spread between the highest and lowest values
CurrentFieldName / ( First( Sort( YourListName, YourValueField, Descending ) ).YourValueField - First( Sort( YourListName, YourValueField, Ascending ) ).YourValueField ) * 100
You could apply this value to the TemplateFill as per my second post.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
mdevaney 29,989 Moderator on at

Like (1)

Report

@BrianHFASPS

Agreed. Your Top-Value/Bottom-Value/Divide method is exactly what Excel does.

Was this reply helpful? Yes No
BrianHFASPS 718 on at

Like (1)

Report
Thanks for all the inspiration. Here is my final working code.
If(PartCount<First(Sort(CountRoster,PartCount,Ascending)).PartCount+(First(Sort(CountRoster,PartCount,Descending)).PartCount-First(Sort(CountRoster,PartCount,Ascending)).PartCount)/5,Red, PartCount<First(Sort(CountRoster,PartCount,Ascending)).PartCount+((First(Sort(CountRoster,PartCount,Descending)).PartCount-First(Sort(CountRoster,PartCount,Ascending)).PartCount)/5)*2,Orange, PartCount<First(Sort(CountRoster,PartCount,Ascending)).PartCount+((First(Sort(CountRoster,PartCount,Descending)).PartCount-First(Sort(CountRoster,PartCount,Ascending)).PartCount)/5)*3,Yellow, PartCount<First(Sort(CountRoster,PartCount,Ascending)).PartCount+((First(Sort(CountRoster,PartCount,Descending)).PartCount-First(Sort(CountRoster,PartCount,Ascending)).PartCount)/5)*4,LightGreen, PartCount>First(Sort(CountRoster,PartCount,Ascending)).PartCount+((First(Sort(CountRoster,PartCount,Descending)).PartCount-First(Sort(CountRoster,PartCount,Ascending)).PartCount)/5)*4,Green, RGBA(0,0,0,0))
It is basically Value < LargeValue-SmallValue/5 is lowest and then I move up in increments. The colors / fonts are perfect but it works pretty well. It isn't as incremental and clean as percentages but it gives me the chunks I want. Just realized I need <= otherwise it doesn't work for exact values.
Color Scales

Was this reply helpful? Yes No
mdevaney 29,989 Moderator on at

Like (0)

Report

@BrianHFASPS

I love this. It's awesome 😍

Was this reply helpful? Yes No
WarrenBelz 153,084 Most Valuable Professional on at

Like (1)

Report

Hi @BrianHFASPS ,
Great to see it working, now I see your exact requirement, it is rather complex - any chance of using Format Text on that and posting it in a Text box? If it is working, don't change it, but I am interested in whether the logic can be done in a simpler way as there is an enormous amount of calculating if a gallery needs to resolve that on every row.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
BrianHFASPS 718 on at

Like (0)

Report

Do you mean like doing the calc in a text box like this:
Color Calc
Then just referencing those text boxes as Value(Text Box) kind of way? I actually started down that path initially and for some reason just copied the calcs over instead. The collections will only be 15-50 rows long so not huge data sets. It is class rosters. This is tracking participation in live Teams / Zoom sessions for my school during distance learning.

Was this reply helpful? Yes No