Key performance indicators, or KPIs, are metrics that indicate how well an organization is performing against its objectives. KPIs are typically represented as easily understandable color-coded symbols in a dashboard. For example, a red circle could mean that area is not meeting objectives, while a green circle does.
In this recipe, we will show the top 10 New York restaurant cuisines based on their average inspection scores from 2012-2016 in a text table with KPIs.
To follow this recipe, open B05527_02 – STARTER.twbx. Use the worksheet called KPI Text Table, and connect to the DOHMH New York City Restaurant data source.
The following are the steps to create a KPI table:
- From Dimensions, drag Inspection Date to the Filters shelf.
- When prompted on what to filter, choose Years.
- In the next window, under the General tab, select all years except 1900.
- From Dimensions, drag Inspection Date to the Columns shelf.
- From Dimensions, drag Cuisine Description to the Filters shelf and under the Top tab, select Top 10 by Average of Score.
- From Dimensions, drag Cuisine Description to the Rows shelf.
- Create a calculated field called Cuisine A Grade. If the average score is between 0 and 13, the score is A. A in this case is a special character that you can copy from http://www.copypastecharacter.com/alphabetical-order.
- The formula for the calculated field is as follows:
- Create another calculated field called Cuisine B Grade, which assigns the special B character if the average score is between 14 and 27.
- Create another calculated field called Cuisine C Grade, which assigns the special C character if the average score is greater than 27.
- Create one more calculated field called Avg Score String, which creates a text field that contains the average score. If the average score is a single digit, we pad the text with spaces. We are creating this to ensure that single and double digit scores align when displayed as text.
- Drag the newly created calculated fields Cuisine A Grade, Cuisine B Grade, Cuisine C Grade, and Avg Score String to Text in the Marks card.
- In the Marks card, click on Label.
- Click on the ellipsis in the Text field to change the label to the following. Note that each cuisine grade can be added using the Insert dropdown. Each cuisine grade is also colored differently—green for an A grade, orange for a B grade, and red for a C grade. Feel free to change the color to your preference.
In this recipe, we have chosen to display a symbol beside each value in our text table. In many ways this is really just a text table, which is covered in Creating a text table recipe in Chapter 1, Basic Charts. How this is different is based on how the text for each cell is created.
We first need to create calculated fields that will determine the grades for the cuisines. According to the Government of New York website, for each inspection:
- 0 to 13 points earns an A
- 14 to 27 points receives a B
- 28 or more is a C
Note
If you are interested in how New York restaurants are graded, you can read the official guide from http://www1.nyc.gov/assets/doh/downloads/pdf/rii/how-we-score-grade.pdf.
We are just going to use the same criteria for the average scores for each cuisine. Each of these calculations is an If statement. If the value falls within a certain range, the If statement returns a special character:
The characters are copied from http://www.copypastecharacter.com/alphabetical-order.
Once the score fields are created, the text field needs to be created. In this text field, all the previous calculated fields are included—each grade is formatted with a different color.
Each of the grades are mutually exclusive. Since the individual grade calculated fields just IF statements and not If...Else statements, if the scores are outside of a specific range, there is nothing that gets displayed. This creates the dynamic label effect.
Instead of multiple calculated fields, you can also choose to just have a single calculated field. In this case, having multiple calculated fields may be helpful especially if you want to reuse these in tooltips and so on.
Another way, and perhaps more common, is to use the shapes to create a KPI table. We will still need a calculated field that will give us the discrete values for the average grade.
This field can be used in a Shape property in the Marks card to display common KPI icons.
- Please refer to the Creating a text table recipe in Chapter 1, Basic Charts