Calculate score per category using a score system

[MAKroeze]

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

[MAKroeze]

Hi Yolo Zhu,

That solved the challenge, thanks for the solution!

Best regards,

Michel

[v-xinruzhu-msft · Accepted solution]

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.

[v-xinruzhu-msft]

Hi