Numeric Dynamic Rating

[Anonymous]

Hello Power BI Community,

I am building a performance scorecard having 3 KPIs, which are available as input and I have a reference scale also to assign the rating based on inputs, so I am looking that how this can be built in Table Visual in Power BI

What I am looking for help with is that if my KPI 1 is 88% then in column KPI 1_Points it should be populated as 20 (based on the reference scale)

KPI 1, KPI 2 and KPI 3 are available and wanted to populate the numbers in columns KPI 1_Points, KPI 2_Points and KPI 3_Points based on the reference scale given below

KPI 1	KPI2	KPI3	KPI 1_Points	KPI 2_Points	KPI 3_Points
88%	3	7
90%	1	5
69%	4	8
77%	2	10
100%	5	15
98%	0	11
81%	9	17

The reference Scale is as given below

KPI1	Points		KPI2	Points		KPI3	Points
100%	50		0	20		< 7	30
95-99%	40		1	15		8 -10	15
91-94%	30		2	10		11- 15	8
80 - 90%	20		3	5		>15	0
70-80%	10		4	0
<70%	0

Appreciate any help...

Thanks & Regards

Samrat

[ddpl · Accepted solution]

@Anonymous ,

Calculated Column : KPI 1 points

KPI1 Points = SWITCH(TRUE(),
                      'Table'[KPI 1] = 1,50,
                      'Table'[KPI 1] < 1 && 'Table'[KPI 1] >= 0.95, 40,
                      'Table'[KPI 1] < 0.95 && 'Table'[KPI 1] >= 0.91, 30,
                      'Table'[KPI 1] < 0.91 && 'Table'[KPI 1] >= 0.80, 20,
                      'Table'[KPI 1] < 0.80 && 'Table'[KPI 1] >= 0.70, 40, 0)

Calculated Column : KPI 2 points

KPI2 Points = SWITCH(TRUE(),
                     'Table'[KPI2] = 0, 20,
                     'Table'[KPI2] = 1, 15,
                     'Table'[KPI2] = 2, 10,
                     'Table'[KPI2] = 3, 5,
                     'Table'[KPI2] = 4, 0)

Calculated Column : KPI 3 points

KPI3 Points = SWITCH(TRUE(),
                     'Table'[KPI3] <=7, 30,
                     'Table'[KPI3] >7 && 'Table'[KPI3] <=10, 15,
                     'Table'[KPI3] >10 && 'Table'[KPI3] <=15, 8, 0)

[Anonymous · Accepted solution]

Thanks, it solved the purpose. Appreciate your help...

[ddpl · Accepted solution]

@Anonymous ,

Calculated Column : KPI 1 points

KPI1 Points = SWITCH(TRUE(),
                      'Table'[KPI 1] = 1,50,
                      'Table'[KPI 1] < 1 && 'Table'[KPI 1] >= 0.95, 40,
                      'Table'[KPI 1] < 0.95 && 'Table'[KPI 1] >= 0.91, 30,
                      'Table'[KPI 1] < 0.91 && 'Table'[KPI 1] >= 0.80, 20,
                      'Table'[KPI 1] < 0.80 && 'Table'[KPI 1] >= 0.70, 40, 0)

Calculated Column : KPI 2 points

KPI2 Points = SWITCH(TRUE(),
                     'Table'[KPI2] = 0, 20,
                     'Table'[KPI2] = 1, 15,
                     'Table'[KPI2] = 2, 10,
                     'Table'[KPI2] = 3, 5,
                     'Table'[KPI2] = 4, 0)

Calculated Column : KPI 3 points

KPI3 Points = SWITCH(TRUE(),
                     'Table'[KPI3] <=7, 30,
                     'Table'[KPI3] >7 && 'Table'[KPI3] <=10, 15,
                     'Table'[KPI3] >10 && 'Table'[KPI3] <=15, 8, 0)