The advanced score meter chart is nothing else than such an application that helps us display each key performance indicator in a different way till now.
We have already seen several solutions – like gauge chart – on the net, but these are all looked alike except for a very few. We keep ourselves to the previously written down principles meaning that on the blog, you will only see unique dashboard templates.
This will be like this today; we will introduce the making of an advanced score meter chart, but we wouldn’t settle only for this, we go further, and the article will contain some very unique and original solutions. Even the array formulas will be at our help!
On these pictures can be seen that we have endeavored to create such a user interface that you can tell at a glance what is displayed and the meaning of it. Let’s say a few words of the widget!
Score Meter Chart – How to use Slicers
The effectuation of the interval division makes the score meter chart unique. We all know the classic scale with degrees ranging from a week to excellent.
We will use these and in a dynamic way! As a default, we will be able to handle values between 0% and 100%.
Our advanced score meter chart will have that special characteristic that we will set each element of the color chart by giving arbitrary values. As usual, red is the smallest / weakest value, the dark green is the best / highest value.
The division is not needed to be even; we can differ from the average division rule of 0%-20%, 21%-40%,…81%-100%. Why is this so useful for us? Using the opportunities given by free configuration, there will be KPIs available for display that differs from the customary.
We have defined the percentages in the ‘Slicers’ column. But we have to bring your attention to one important issue! When changing values (anyone out of the five), pay attention so that the value doesn’t exceed 100% because we can maximum only display this on the chart.
In our example, we increased the middle interval (yellow color) to 30%, so we had to reduce the interval containing the weakest value to 10%. Of course, it is not mandatory to reduce exactly this; the bottom line is their sum must be exactly 100%.
The score meter only displays correctly in this case! We have applied a control field in the ‘Slicers’ column; if its value is 100%, then everything is all right.
Thousands of variations can be imagined by modifying the percentage, always use the appropriate ones for the task. Good to know that the score meter chart looks spectacular as a part of a complex dashboard!
We have created the ‘MIN’ and ‘MAX’ columns that we have made intervals using the rules of running total. Based on the current ‘Slicers’ settings these are the following: 0-10%; 11%-30%; 31%-60%; 61%-80%; 81%-100%.
Using Arrays – The Frequency Formula
If you are using array formulas, you need advanced knowledge, and we will talk about them in detail, for they are important in this solution. Set the actual value to 70%.
The array formula will decide that in the ‘FREQUENCY’ column, what value will correspond to this percentage.
In our case, between 61% and 80%, that’s why the number 1 indicator can be seen there.
How do we get this result? Highlight the cells as we do in the picture. After this, click on the formula bar and write in the formula. DO NOT press enter!
But instead use the <Ctrl> + <Shift> + <Enter> combination! Then the results will be automatically entered in all cells. And you will notice that the formula appears with curly brackets around it.
Assigning TEXT to values
There’s only one thing left to do, and this is to display the text assigned to the percentage values on the advanced score meter chart. We would like to accomplish that the text of the category appears in a text box. We have to bring out the good old VLOOKUP formula.
The first parameter of the formula will be the ‘1’ indicator (located in the FREQUENCY column); the second parameter, in turn, will look for text assigned to the given interval in the ‘TEXT’ column. And we are all done!
Thank you for staying with us today. The above-mentioned chart can be downloaded from this link. Just play with our advanced score meter chart!