Forum Discussion
Numeric Dynamic Rating
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
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 pointsKPI2 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 pointsKPI3 Points = SWITCH(TRUE(),
'Table'[KPI3] <=7, 30,
'Table'[KPI3] >7 && 'Table'[KPI3] <=10, 15,
'Table'[KPI3] >10 && 'Table'[KPI3] <=15, 8, 0)
2 Replies
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 pointsKPI2 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 pointsKPI3 Points = SWITCH(TRUE(),
'Table'[KPI3] <=7, 30,
'Table'[KPI3] >7 && 'Table'[KPI3] <=10, 15,
'Table'[KPI3] >10 && 'Table'[KPI3] <=15, 8, 0)