Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MAKroeze
Regular Visitor

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:

ThemeMonthPercentage_issues_solved
A1-1-202393
A2-1-2023100
A3-1-2023100
A4-1-202395
A5-1-202395
A6-1-202393
A7-1-202394
A8-1-202394
A9-1-202394
A10-1-202394
A11-1-202398
A12-1-202399
B1-1-202399
B2-1-2023100
B3-1-202396
B4-1-2023100
B5-1-202398
B6-1-202398
B7-1-202399
B8-1-2023100
B9-1-2023100
B10-1-2023100
B11-1-2023100
B12-1-2023100

 

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

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 )

vxinruzhumsft_0-1695867714173.png

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.

 

View solution in original post

3 REPLIES 3
MAKroeze
Regular Visitor

Hi Yolo Zhu,

That solved the challenge, thanks for the solution!

 

Best regards,

Michel

v-xinruzhu-msft
Community Support
Community Support

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 )

vxinruzhumsft_0-1695867714173.png

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
Community Support
Community Support

Hi 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors