Forum Discussion
Calculate score per category using a score system
I have a dataset with multiple theme's.
For each theme it shows the percentage of issues that were solved each month.
Now I have to calculate a yearly score for each theme using the following scoring system.
One of the challenges is that multiple score categories can be true.
When that occurs, the lowest point should be assigned.
Any suggestion on how I can solve this?
Score system
- 10 points: percentage solved >= 98 for all months
- 7 points: percentage solved >= 95 for all months
- 5 points: percentage solved >= 95 for two or more months
- 0 points: percentage solved < 95 for more than 2 months
Mockup data:
| Theme | Month | Percentage_issues_solved |
| A | 1-1-2023 | 93 |
| A | 2-1-2023 | 100 |
| A | 3-1-2023 | 100 |
| A | 4-1-2023 | 95 |
| A | 5-1-2023 | 95 |
| A | 6-1-2023 | 93 |
| A | 7-1-2023 | 94 |
| A | 8-1-2023 | 94 |
| A | 9-1-2023 | 94 |
| A | 10-1-2023 | 94 |
| A | 11-1-2023 | 98 |
| A | 12-1-2023 | 99 |
| B | 1-1-2023 | 99 |
| B | 2-1-2023 | 100 |
| B | 3-1-2023 | 96 |
| B | 4-1-2023 | 100 |
| B | 5-1-2023 | 98 |
| B | 6-1-2023 | 98 |
| B | 7-1-2023 | 99 |
| B | 8-1-2023 | 100 |
| B | 9-1-2023 | 100 |
| B | 10-1-2023 | 100 |
| B | 11-1-2023 | 100 |
| B | 12-1-2023 | 100 |
Hi MAKroeze
You can refer to the following sample.
1.Create a calculated column in table.
Type = SWITCH ( TRUE (), [Percentage_issues_solved] >= 98, ">=98", [Percentage_issues_solved] >= 95 && [Percentage_issues_solved] < 98, ">=95 and <98", [Percentage_issues_solved] < 95, "< 95" )2.Then create a measure
Measure = VAR a = COUNTROWS ( FILTER ( ALLSELECTED ( 'Table' ), [Theme] IN VALUES ( 'Table'[Theme] ) && [Type] = ">=98" ) ) VAR b = COUNTROWS ( FILTER ( ALLSELECTED ( 'Table' ), [Theme] IN VALUES ( 'Table'[Theme] ) && OR ( [Type] = ">=95 and <98", [Type] = ">=98" ) ) ) VAR c = COUNTROWS ( FILTER ( ALLSELECTED ( 'Table' ), [Theme] IN VALUES ( 'Table'[Theme] ) && [Type] = "< 95" ) ) VAR d = COUNTROWS ( FILTER ( ALLSELECTED ( 'Table' ), [Theme] IN VALUES ( 'Table'[Theme] ) ) ) RETURN SWITCH ( TRUE (), a = d, 10, b = d, 7, b >= 2, 5, c >= 2, 0 )Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi MAKroeze
You can refer to the following sample.
1.Create a calculated column in table.
Type = SWITCH ( TRUE (), [Percentage_issues_solved] >= 98, ">=98", [Percentage_issues_solved] >= 95 && [Percentage_issues_solved] < 98, ">=95 and <98", [Percentage_issues_solved] < 95, "< 95" )2.Then create a measure
Measure = VAR a = COUNTROWS ( FILTER ( ALLSELECTED ( 'Table' ), [Theme] IN VALUES ( 'Table'[Theme] ) && [Type] = ">=98" ) ) VAR b = COUNTROWS ( FILTER ( ALLSELECTED ( 'Table' ), [Theme] IN VALUES ( 'Table'[Theme] ) && OR ( [Type] = ">=95 and <98", [Type] = ">=98" ) ) ) VAR c = COUNTROWS ( FILTER ( ALLSELECTED ( 'Table' ), [Theme] IN VALUES ( 'Table'[Theme] ) && [Type] = "< 95" ) ) VAR d = COUNTROWS ( FILTER ( ALLSELECTED ( 'Table' ), [Theme] IN VALUES ( 'Table'[Theme] ) ) ) RETURN SWITCH ( TRUE (), a = d, 10, b = d, 7, b >= 2, 5, c >= 2, 0 )Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Yolo Zhu,
That solved the challenge, thanks for the solution!
Best regards,
Michel
