Creating scoreboard for measures

[Anonymous]

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.

[Ashish_Mathur]

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?

[Anonymous]

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 Apps	30%
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.

[Anonymous]

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.

[Anonymous]

I would also like to ask @jdbuchanan71 @parry2k @Cmcmahan @Zubair_Muhammad @Mariusz 

if you could give me any suggestions or inputs.

I really need to deliver this to business soon.

Thanks in advance.

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

Please take a look at this video from @avisingh 
https://www.youtube.com/watch?v=WV5KxZRIzUE

He 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 $.