Forum Discussion
Creating scoreboard for measures
Ehllo everyone,
I was recently asked to create a scoreboard for a few metrics based on a KPI Scoring method. But, I am unable to set this in a Power Bi dashbaord format. It was relatively easier in excel as the measures can be places on rows in excel files.
Below is the Scoring criteria:
| Score | Definition |
| 5 | >=10% above plan |
| 4 | 5% above plan |
| 3 | Meets plan |
| 2 | 5% below plan |
| 1 | >10% below plan |
Below is what I am expected to create:
| KPI | Actual | Plan | %Performance Change | Score |
| Applications | 10 | 13 | -30% | 1 |
| ApprovalRate | 34% | 23% | 32% | 5 |
| Funded Dollars | $ 84,000 | $ 80,000 | 5% | 4 |
| Average Score = | 3.33 |
The Scores in the above table are set based on the values in the '% Performance Change' column.
Also, I am supposed to add a filter for Year and Month, so the business can view historical data as well.
The plan values will be provided to me by the business.
Can someone please build the second table for me.
Thanks in advance.
Hello Anonymous
Please take a look at this video from avisingh
https://www.youtube.com/watch?v=WV5KxZRIzUEHe goes through using a pair of label tables and nested switches to achieve what you are looking for. You would also want to apply some FORMAT in the switches based on if you ar returning a %, count or $.
5 Replies
Hi,
Share your raw data in a format that i can paste it in an Excel workbook. Also, how have you calculated %Performance Change? Is it (Plan-Actual)/Actual?
Hi Ashish_Mathur ,
The sample raw data looks like:
AppID ApprovedAmount FundedAmnt 1 $ 20,000 $ 20,000 2 $ 0 $ 0 3 $ 0 $ 0 4 $ 0 $ 0 5 $ 0 $ 0 6 $ 0 $ 0 7 $ 40,000 $ 40,000 8 $ 24,000 $ 24,000 9 $ 0 $ 0 10 $ 0 $ 0
For the above 10 applications,Applications = 10 Approval Rate = (count of apps with an
Approved Amount > 0)/Total Apps30% Funded Amount = Sum of All funded
Amount$ 84,000 So, there are planned value for Applications, Approval Rate, and the TotalFundedAmount.
Also, the % Performance Change = (Actual - Plan)/Plan.
Let me know if you have any other questions.
I forgot to add the date column to the raw data table shared above.
AppID ApprovedAmount FundedAmnt SubmittedDate 1 $ 20,000 $ 20,000 1/1/2019 2 $ - $ - 1/2/2019 3 $ - $ - 1/3/2019 4 $ - $ - 1/4/2019 5 $ - $ - 1/5/2019 6 $ - $ - 1/6/2019 7 $ 40,000 $ 40,000 1/7/2019 8 $ 24,000 $ 24,000 1/8/2019 9 $ - $ - 1/9/2019 10 $ - $ - 1/10/2019 Measures: Applications, Approval Rate and TotalFundedAmount are based on the 'Submitted Date' column.
