cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
_larissa
Frequent Visitor

Categorizing Sharepoint list field by colors based on scores

Hi, 

I have a SharePoint list that are connected to an app I built using PowerApps. The app will save user's information and calculated score (Depression, Anxiety, Stress) in the SharePoint list as shown below:

Screenshot (292).png

I'm trying to categorize the Depression, Anxiety and Stress fields based on a specific scores like 0-4 = Normal, 5-6 = Moderate and 11-13 = Severe. They can be categorized by color coding the field or just simply by having the severity level mentioned next to the calculated scores.

 

I already went through the postings and asked for help from another group as well but still couldn't find the solution I'm looking for. So I appreciate if someone can help me to make this work. Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
v-albai-msft
Community Support
Community Support

Hi @_larissa ,

Do you use a gallery to show your list data? Do you want to categorize the scores display in this gallery?  

If yes, you can use the Fill property to change its color based on the value. For example, I have a list shown inside a gallery. This list has a calculated column called "Depression":

v-albai-msft_4-1622180640367.png

 

v-albai-msft_1-1622180323336.png

Edit this control and set its Fill property to below formula:

If(
Value(ThisItem.Depression)>= 0 && Value(ThisItem.Depression) <= 4,RGBA(196, 237, 165, 1),
Value(ThisItem.Depression) >=5 && Value(ThisItem.Depression)<=6,RGBA(249, 202, 128, 1),
Value(ThisItem.Depression) >=11 && Value(ThisItem.Depression)<= 13,RGBA(249, 142, 128, 1))

v-albai-msft_2-1622180478035.png

Repeat above steps for other columns.

 

If you want to show different text based on different scores, you can set its Text property to below formula:

If(
Value(ThisItem.Depression)>= 0 && Value(ThisItem.Depression) <= 4,"Normal",
Value(ThisItem.Depression) >=5 && Value(ThisItem.Depression)<=6,"Moderate",
Value(ThisItem.Depression) >=11 && Value(ThisItem.Depression)<= 13,"Severe")

v-albai-msft_3-1622180621062.png

 

If you want to do such categorization on SharePoint side, I would suggest you create another new thread in SharePoint Community:

https://techcommunity.microsoft.com/t5/sharepoint/bd-p/SharePoint_General 

Best regards,

Allen 

View solution in original post

3 REPLIES 3
TheRobRush
Power Participant
Power Participant

You will need to do something like this, with code

 

Use column formatting to customize SharePoint 

 

 

_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!

I've tried the JSON code but it still doesn't work. This is the code that I've constructed according to my list, can you tell me where I did wrong?

 

{
"elmType": "div",
"attributes": {
"class": "=if(@currentField <= 4,'sp-field-severity--low',if(@currentField = 5,'sp-field-severity--good',if(@currentField = 6,'sp-field-severity--good',if(@currentField = 7,'sp-field-severity--warning',if(@currentField = 8,'sp-field-severity--warning',if(@currentField = 9,'sp-field-severity--warning',if(@currentField = 10,'sp-field-severity--warning',if(@currentField = 11,'sp-field-severity--severeWarning',if(@currentField = 12,'sp-field-severity--severeWarning',if(@currentField = 13,'sp-field-severity--severeWarning',if(@currentField = 14,'sp-field-severity--blocked', '')))))))))))"
},
"children": [
{
"elmType": "span",
"style": {
"display": "inline-block",
"padding": "0 4px"
}
},
{
"elmType": "span",
"txtContent": "@currentField"
}
]
}
Also, I tried changing @currentField to my own field name and it still won't work.
v-albai-msft
Community Support
Community Support

Hi @_larissa ,

Do you use a gallery to show your list data? Do you want to categorize the scores display in this gallery?  

If yes, you can use the Fill property to change its color based on the value. For example, I have a list shown inside a gallery. This list has a calculated column called "Depression":

v-albai-msft_4-1622180640367.png

 

v-albai-msft_1-1622180323336.png

Edit this control and set its Fill property to below formula:

If(
Value(ThisItem.Depression)>= 0 && Value(ThisItem.Depression) <= 4,RGBA(196, 237, 165, 1),
Value(ThisItem.Depression) >=5 && Value(ThisItem.Depression)<=6,RGBA(249, 202, 128, 1),
Value(ThisItem.Depression) >=11 && Value(ThisItem.Depression)<= 13,RGBA(249, 142, 128, 1))

v-albai-msft_2-1622180478035.png

Repeat above steps for other columns.

 

If you want to show different text based on different scores, you can set its Text property to below formula:

If(
Value(ThisItem.Depression)>= 0 && Value(ThisItem.Depression) <= 4,"Normal",
Value(ThisItem.Depression) >=5 && Value(ThisItem.Depression)<=6,"Moderate",
Value(ThisItem.Depression) >=11 && Value(ThisItem.Depression)<= 13,"Severe")

v-albai-msft_3-1622180621062.png

 

If you want to do such categorization on SharePoint side, I would suggest you create another new thread in SharePoint Community:

https://techcommunity.microsoft.com/t5/sharepoint/bd-p/SharePoint_General 

Best regards,

Allen 

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.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (3,305)