With the Scorecard component, we can easily create scorecard KPIs with the ability to insert a user-desired alert / trending icon in any column of the scorecard.
In this recipe, we will go through an example of using the Scorecard component to show a table of values, a threshold indicator that will determine whether the current month's sales meet the threshold or not, and a trend indicator that shows whether the current month's sales have risen/fallen compared to the previous month's sales.
Set up your data as follows. We have a list of regions, current month sales, previous month sales, and sales threshold. Note that the Month to Month Change column is highlighted because it can either come from the external data source or it could be calculated in the Excel spreadsheet.
=IF(B2 >= E2, 1, -1)
, in each row in column F.0
.In the Scorecard component, we can combine different types of alerts together with our table data, as shown in the following screenshot. Now, let's discuss some important points from the steps in the How to do it… section.
In step 2, you will notice that we do not bind to the Sales Threshold column because that column is used to calculate whether the Current Month Sales are above or below the Sales Threshold. As you can see in step 3, we have column F that houses the calculation. The calculated values in column F are then bound as the alert values in step 5. From there, we are able to determine whether the threshold indicator on the Current Month Sales column should be green or red.
The trend icons shown in the Month to Month Change column are determined from the values in the Month to Month Change column. In step 13, you will notice the funny 0.00001 values. This is to take into account Month to Month Change values that are 0. Unfortunately, we cannot set (if value = 0, then show no change arrow), so 0.00001 is the next closest alternative. In the Month to Month Change column, we want to show the arrows, so in step 16, we hide the text values.
You can customize the scorecard look by modifying the appearance settings. Read the recipes in Chapter 7, Dashboard Look and Feel, to learn how to change the appearance settings of a component.