How to make a KPI appear next to a value in a column

I am trying to work my way around, adding KPIs next to a desired value.

I have tried this so far by using this in the calculated column formula field

<img src=" "/_Layouts/images/kpidefault-" & If ([Product cost] - [Materials cost] > 0

Basically i want to say a Green KPI should appear if the difference is > 0.

Any help appreciated. Many thanks.

• "<img src=" "/_Layouts/images/kpidefault-" & If ([Product cost] - [Materials cost] > 0" this is what I tried Jun 14 '16 at 8:02
• formula is incomplete Jun 14 '16 at 8:11
• explain what exctly your poutput is expected, url img src seems to incomplete Jun 14 '16 at 8:19

You can use the following formula:

=IF([Product cost] - [Materials cost]>0,[Product cost] - [Materials cost]&"<img src='https://cdn2.iconfinder.com/data/icons/splashyIcons/arrow_large_up.png'>","")

(since your icon URL is invalid, I changed it to the URL of an icon on the internet)

Don't forget to set the data type of the calculated column to Number, otherwise it won't work.

• Formula won't save because there is an extra ( To output HTML Datatype can be anything but Text. Number will add an extra DIV and align the HTML to the Right. Other datatypes have different alignment Jun 14 '16 at 9:41
• Thanks for pointing out that extra bracket. I removed it. Jun 14 '16 at 9:47

=If ([Product cost] - [Materials cost] > 0,[Product cost] - [Materials cost],"")

This formula will give you the difference of [Product cost], [Materials cost] if it is greater than zero, else it will be blank.

To combine what you want, try this formula.

= '<img src="/_Layouts/images/kpidefault-"' & If ([Product cost] - [Materials cost] > 0,[Product cost] - [Materials cost],"") & '/>'

PS: Source is used as is from question. Make sure your image source is valid.

• This Formula will never save because there are no outer quotes Jun 14 '16 at 9:37

The syntax for displaying an Image with a Calculated Column (in a View only) is:

="<img src='image.gif'>"

(you need to set the Datatype to output as anything but Text, different datatype will output different Alignments)

There are 3 default images available by SharePoint as

="<img src='/_layouts/images/kpidefault-0.gif'>"
="<img src='/_layouts/images/kpidefault-1.gif'>"
="<img src='/_layouts/images/kpidefault-2.gif'>"

You want the green image to be displayed based on a calculation: this is pseudo code:

IF( [Product cost]-[Materials cost] > 0 , GREEN , NOTHING )

So the full Calculated Column Formula is:

=IF([Product cost]-[Materials cost]>0,"<img src='/_layouts/images/kpidefault-0.gif'>","")

To add the difference as label:

=[Product cost]-[Materials cost] &
IF( [Product cost]-[Materials cost]>0
, "<img src='/_layouts/images/kpidefault-0.gif'>"
, ""
)
• It is perfectly valid to add linebreaks and spaces, SharePoint will ignore them when you paste the Formula in the Calculated Column and save it.
If you always start from a texteditor (Notepad) like this, you have more insight in the flow of your Formula.

• Excel can be helpfull in building Formulas, as most basic Functions are the same See

https://www.365csi.nl/vm365com/365coach/#/Calculated_Column_Functions_List

• It is a PITA to debug Calculated Columns. Because you don't get feedback until you save a Formula (with an undescriptive message) and you end up having to click multiple times to get back to your Formula. I have written a small 'CalcMaster' bookmarklet which hooks into the formula-editor and does a save of the Formula on every keypress; giving immediate feedback. See: https://github.com/Danny-Engelman/CalcMaster

created from a 0, 1 ... Number value

If you want to create an image based on a Number field,

The Formula is (pseudo code):

="<img src='/_layouts/images/kpidefault-" & NUMBER & ".gif'>"

CSR

With (SP2013+) Client Side Rendering, all this can be simplified with some minor JavaScript code and the OpenSource iCSR.Github.io Library

iCSR.Me({
Fields: {
"Priority":{
View : iCSR.Priority
}});

Produces an interactive View: Update #1 HTML only

Here is HTML code to color the profit as text using CSS

="<span style='"
& IF( [Product cost]-[Materials cost]>0
, "color:green;font-weight:bold"
, IF( [Product cost]-[Materials cost]<0
, "color:red"
, "color:black"
)
)
& "'>"
& [Product cost]-[Materials cost]
& "</span>"
• How can I choose your answer as the best answer. Thanks alot. It solves my query. Very good tutorial Jun 14 '16 at 9:49