Conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-2165832%22%20slang%3D%22en-US%22%3EConditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2165832%22%20slang%3D%22en-US%22%3E%3CP%3EI'd%20like%20to%20apply%20conditional%20formatting%20to%20an%20auto%20calculated%20column%20that%20needs%20to%20display%20the%20value%20as%20a%20%25.%26nbsp%3B%20I'd%20like%20to%20highlight%20values%20%26gt%3B%3D%2075%25%20and%20%26lt%3B90%25%20as%20yellow%20and%20values%20%26gt%3B%3D90%25%20as%20red.%26nbsp%3B%20I'm%20unfamiliar%20with%20JSON%20and%20am%20unable%20to%20get%20the%20syntax%20correct.%26nbsp%3B%20I%20can%20get%20it%20to%20highlight%20the%20cells%20correctly%20but%20it%20displays%20the%20values%20as%20a%20decimal%20number%20and%20not%20a%20%25.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2166096%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2166096%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F978635%22%20target%3D%22_blank%22%3E%40Walterwhamil72%3C%2FA%3E%26nbsp%3Bthis%20can%20be%20done%20as%20follows.%20In%20your%20calculated%20column%20use%20the%20following%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22SPlistSettings.png%22%20style%3D%22width%3A%20373px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257889iB756BBFEA9D77071%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22SPlistSettings.png%22%20alt%3D%22SPlistSettings.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3BThe%20formatting%20of%20the%20column%20is%20then%20like%20this%3A%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-json%22%3E%3CCODE%3E%7B%0A%20%20%22%24schema%22%3A%20%22https%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fcolumn-formatting.schema.json%22%2C%0A%20%20%22elmType%22%3A%20%22div%22%2C%0A%20%20%22txtContent%22%3A%20%22%3D%5B%24Percent%5D*100%20%2B%20'%25'%22%2C%0A%20%20%22style%22%3A%20%7B%0A%20%20%20%20%22color%22%3A%20%22%3Dif(%5B%24Percent%5D%20%26gt%3B%3D%200.75%20%2C%20'%23ffffff'%2C%20'%23525252'%22%2C%0A%20%20%20%20%22background-color%22%3A%20%22%3D%20if((%5B%24Percent%5D%20%26gt%3B%3D%200.75%20%26amp%3B%26amp%3B%20%5B%24Percent%5D%20%26lt%3B%200.90%20%2C%20'%23eebf2d'%2C%20if((%5B%24Percent%5D%20%26gt%3B%200.90%2C%20'%23730000')%2C%20'%23ffffff'%22%0A%20%20%7D%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhich%20gives%20the%20following%20result%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22SPlist.png%22%20style%3D%22width%3A%20561px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257886i9E31CDFAC03A20E7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22SPlist.png%22%20alt%3D%22SPlist.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I'd like to apply conditional formatting to an auto calculated column that needs to display the value as a %.  I'd like to highlight values >= 75% and <90% as yellow and values >=90% as red.  I'm unfamiliar with JSON and am unable to get the syntax correct.  I can get it to highlight the cells correctly but it displays the values as a decimal number and not a %.

4 Replies

@Walterwhamil72 this can be done as follows. In your calculated column use the following formula:

 

SPlistSettings.png

 

 The formatting of the column is then like this:

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=[$Percent]*100 + '%'",
  "style": {
    "color": "=if([$Percent] >= 0.75 , '#ffffff', '#525252'",
    "background-color": "= if(([$Percent] >= 0.75 && [$Percent] < 0.90 , '#eebf2d', if(([$Percent] > 0.90, '#730000'), '#ffffff'"
  }
}

 

which gives the following result:
SPlist.png

Rob
Los Gallardos
Microsoft Power Automate Community Super User

@RobElliott Thanks for the guidance.  I followed your instructions but the column is now blank.  The column names I need in the calculation are:

 

Cumulative Invoiced/Total Value

 

The formula I'm using looks like this:

 

Formula.PNG

 My JSON looks like this:

Json.PNG

 The Burn Rate column looks like this now:

Burnrate.PNG

 Walt.

@Walterwhamil72 the reason it's not working is that in your JSON you've used my column which is called Percent. You've got to use your burn rate column name - as you've got a space in the name check what SharePoint has called it internally in List settings, select the column and check what is after field= in the address bar.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

@RobElliott Everything is working correctly.  Thanks for your assistance.