Calculate Score based on complexity level.

[krishanw]

Hi 

I need to calculate below scenario and I was able to calculate into some level, but when it comes to person A working on different complexity level calculation is wrong

It should be as below

(500*100)+ (300*100)/ (5*100+4*100)

80000/900

Monthly Bug Score = 88.8

Ex: 1

Person A works in Payroll and completed it with Zero Bugs

Payroll module Complexity is 5

According to the bugs scoring chart person A score is 100 for Task A

Module: Payroll

Monthly Bug Count = 0

Bug Score = 100

Bug Score = 100 * 5

Task A bugs score =500

Ex: 2

Person A worked on another task the same month in EIM module and completed task with 20 bugs

EIM module complexity is 4

According to the bugs scoring chart person A score is 75 for task B

 Module: EIM

 Complexity: 4

Monthly Bug Count = 20

Bug Score = 75

Ex:1 Bug Score = 75 * 4

              Task B bugs score =300

• Monthly Bug Score

Maximum Bug Score= Complexity*100

Bug Score * 100/Complexity*100

Ex:

(500*100)+ (300*100)/ (5*100+4*100)

80000/900

Monthly Bug Score = 88.8

[v-shex-msft · Accepted solution]

HI @krishanw,

It sounds like a common issue that appears when you use measure formula to calculate with multiple aggregations.

If that is the csae, you can refer to the Greg's blog that mention this issue and the solution to fix formulas and handle with these calculations:

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng

[krishanw]

Found a solution to this, Issue was when getting the sum of the ModulePriorty and Monthly Bug rating this was a help to find the solution

[krishanw]

This is not worked for me

[v-shex-msft]

HI @krishanw,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

BTW, I also simple your formula based on the conditions, you can try to use it if work as the old one:

Bug Score Rating =
VAR currModule = [ModulePrirotiy]
RETURN
    IF (
        currModule <= 5,
        IF (
            'Efficiency Matrix'[sum of scorecard] >= 0
                && 'Efficiency Matrix'[sum of scorecard] <= 10,
            100 * currModule,
            IF (
                'Efficiency Matrix'[sum of scorecard] <= 30,
                75 * currModule,
                IF (
                    'Efficiency Matrix'[sum of scorecard] <= 70,
                    50 * currModule,
                    IF ( 'Efficiency Matrix'[sum of scorecard] <= 71, 0 )
                )
            )
        )
    )

Regards,

Xiaoxin Sheng

[krishanw]

I think ModulePrirotiy not retrieving correctly

ModulePrirotiy = AVERAGE('Efficiency Matrix'[PriorityScore])

PriorityScore = LOOKUPVALUE(Table1[Priority],Table1[Area Path],'Efficiency Matrix'[Area Path])

Any suggestions for this?-

[krishanw]

This is my measure calculation

Bug Score Rating = SWITCH(TRUE(),

--Complexity 5

[ModulePrirotiy]=5 && 'Efficiency Matrix'[sum of scorecard]=0,100*5,

[ModulePrirotiy]=5 && 'Efficiency Matrix'[sum of scorecard]>=1 && 'Efficiency Matrix'[sum of scorecard]<=10,100*5,

[ModulePrirotiy]=5 && 'Efficiency Matrix'[sum of scorecard]>=11 && 'Efficiency Matrix'[sum of scorecard]<=30,75*5,

[ModulePrirotiy]=5 && 'Efficiency Matrix'[sum of scorecard]>=31 && 'Efficiency Matrix'[sum of scorecard]<=70,50*5,

[ModulePrirotiy]=5 && 'Efficiency Matrix'[sum of scorecard]<=71,0,

--Complexity 4

[ModulePrirotiy]=4 && 'Efficiency Matrix'[sum of scorecard]=0,100*4,

[ModulePrirotiy]=4 && 'Efficiency Matrix'[sum of scorecard]>=1 && 'Efficiency Matrix'[sum of scorecard]<=10,100*4,

[ModulePrirotiy]=4 && 'Efficiency Matrix'[sum of scorecard]>=11 && 'Efficiency Matrix'[sum of scorecard]<=30,75*4,

[ModulePrirotiy]=4 && 'Efficiency Matrix'[sum of scorecard]>=31 && 'Efficiency Matrix'[sum of scorecard]<=70,50*4,

[ModulePrirotiy]=4 && 'Efficiency Matrix'[sum of scorecard]<=71,0,

--Complexity 3

[ModulePrirotiy]=3 && 'Efficiency Matrix'[sum of scorecard]=0,100*3,

[ModulePrirotiy]=3 && 'Efficiency Matrix'[sum of scorecard]>=1 && 'Efficiency Matrix'[sum of scorecard]<=10,100*3,

[ModulePrirotiy]=3 && 'Efficiency Matrix'[sum of scorecard]>=11 && 'Efficiency Matrix'[sum of scorecard]<=30,75*3,

[ModulePrirotiy]=3 && 'Efficiency Matrix'[sum of scorecard]>=31 && 'Efficiency Matrix'[sum of scorecard]<=70,50*3,

[ModulePrirotiy]=3 && 'Efficiency Matrix'[sum of scorecard]<=71,0,

--Complexity 2

[ModulePrirotiy]=2 && 'Efficiency Matrix'[sum of scorecard]=0,100*2,

[ModulePrirotiy]=2 && 'Efficiency Matrix'[sum of scorecard]>=1 && 'Efficiency Matrix'[sum of scorecard]<=10,100*2,

[ModulePrirotiy]=2 && 'Efficiency Matrix'[sum of scorecard]>=11 && 'Efficiency Matrix'[sum of scorecard]<=30,75*2,

[ModulePrirotiy]=2 && 'Efficiency Matrix'[sum of scorecard]>=31 && 'Efficiency Matrix'[sum of scorecard]<=70,50*2,

[ModulePrirotiy]=2 && 'Efficiency Matrix'[sum of scorecard]<=71,0,

--Complexity 1

[ModulePrirotiy]=1 && 'Efficiency Matrix'[sum of scorecard]=0,100,

[ModulePrirotiy]=1 && 'Efficiency Matrix'[sum of scorecard]>=1 && 'Efficiency Matrix'[sum of scorecard]<=10,100,

[ModulePrirotiy]=2 && 'Efficiency Matrix'[sum of scorecard]>=11 && 'Efficiency Matrix'[sum of scorecard]<=30,75,

[ModulePrirotiy]=1 && 'Efficiency Matrix'[sum of scorecard]>=31 && 'Efficiency Matrix'[sum of scorecard]<=70,50,

[ModulePrirotiy]=1 && 'Efficiency Matrix'[sum of scorecard]<=71,0

)

MOnthly Bugs Score =

([Bug Score Rating]*100)/([ModulePrirotiy]*100)