Automated Annual Scorecard with SQL/ DOMO - PART 4 : Score Calculation and Formatting
In the previous post, we have calculated the actuals for every metric.
Now it is time to compare the actuals to the target to determine the scores. We will need monthly, MTD (month to date), QTD (quarter to date), YTD (year to date) and final score.
The scores will be calculated by the performance (Actual/Target or the reverse) * weight.
PART 1 : Formatting + Bring in Targets and Weights
Before we dive straight into the calculations, we need to sort out the formatting first, this has an impact on the calculation too. The end product is going to be in a table format, so we will need the leading columns and leading rows properly labelled.
Note that for each leading row, we need to split up the partners. This is necessary for partner score calculation. For each metric, the same rule applies.
Create a row for every single metric, then we will get something like this:
Now we append the leading rows and columns. Remember that leading columns come from our Scorecard Target sheets, so now we have our targets and weights in the transform.
PART 2 : Monthly % to Target Calculation
With our targets in the transform, we are now ready to calculate the monthly % to target. For each month, we will have two transforms:
Month Query
Month Data
Note that the calculation of index 2. 1a and 2. 2b are Actual divided by Target, because with these metrics, the higher the better. (Order SLA and Escalation SLA performances.) Meanwhile, the calculation of index 2. 1b and 2. 2a are the inverse (Target/Actual), because with these metrics, the lower the better. (Order cancellation rates, and escalations opened rates).
You can apply this rule to your performance calculation for metrics with the same qualities.
PART 3 : Quarterly Actual and Target Calculation
In the months March, June, September, and December, we not only need to calculate the monthly score, but also need to calculate the quarterly score. I will use March as a demonstration. The March Query will be the same as any monthly query, but the March Data calculation will look something like this:
PART 4 : Yearly Actual and Target Calculation
Once we sorted out the quarterly actuals and targets, we are left with the final ‘Yearly’ actuals and targets. This will be completed in the ‘Dec Data’ transform:
PART 5 : Percentage to Target and Final Score Calculation
With all the quarterly/yearly actual and targets, we can start calculating the quarter % to target and year % to target. Remember, depending on the nature of our metrics, we either want to calculate Actual divided by Target or the opposite.
PART 6 : Assemble the Scorecard in DOMO
Now that you have your dataset ready, the last step is just visualization. Thanks to our effort in logically sorting out the formatting in our dataset, creating the visualization became as simple as dragging and drop all of the columns.
We can use beast mode calculation to create rules for the index, in order for us to showcase the title rows and subtitle rows. We can also add quick filter for partner.
Wallah! There is your automated scorecard. We no longer have to juggle with tons of spreadsheets and numbers every month. Set this up in January and we are good for the whole year!
