Looking for help with confusing grouping/ calculation based on 3 columns

[Anonymous]

HI, 

I am trying to graph an engineer score over time. I'm having a hard time calculating the score.

I am unpacking data from a custom built api, and have the following columns:

Engineer | Assigned Date | Weight | Attribute

A                1-2-19                   1              rti

A                  1-5-20                 3              rti

A                  1-6-20                  3             Missed

A                    1-7-20                5            Open

b                  1-6-21                 16             rti

b                   1-6-21                 4              missed

Every single assignment has it's own row, and there are several thousand.

Every assignemt has an attribute that is either Missed, Open, or RTI (ready to integrate)

There are only ~15 engineers 

I'd like to generate a score for each engineer, consisting of

Sum (all weights where attribute = RTI /  Sum(all weights where attribute = missed

So using the above example:

A score = 4/3

B score = 16/4

Many weights are 1, so dividing case by case and then summing will not work. 

Any ideas on how I can do this?

Thanks in advance!

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

I created some data:

When there is no "Missed" in the Attribute of the Engineer, it will not appear first in the visual object by default.

If you want to show it, you can click Show item with no data of [Enginener], but the display is blank.

Result：

If you want to change the blank value to 0, you can use the following dax, add a 0 to the divide, it will automatically convert the blank value to 0:

Measure =
var _rti=CALCULATE(SUM('Table'[Weight]),FILTER(ALL('Table'),'Table'[Engineer]=MAX('Table'[Engineer])&&'Table'[Attribute]="rti"))
var _Missed=CALCULATE(SUM('Table'[Weight]),FILTER(ALL('Table'),'Table'[Engineer]=MAX('Table'[Engineer])&&'Table'[Attribute]="Missed"))
return
DIVIDE(_rti,_Missed,0)

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[tackytechtom]

Hi @Anonymous ,

I would have created one measure like this:

tommeasure = 
DIVIDE (
    CALCULATE(SUM('Table'[Weight]), 'Table'[Attribute] = "rti"),
    CALCULATE(SUM('Table'[Weight]), 'Table'[Attribute] = "Missed")
)  

Does this bring you closer to what you want? 🙂

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

[Anonymous]

Thanks @tackytechtom 

This results in a meausre which I don't think correlates directly to an engineer, at least that's what I am seeing when I drag it into a visual. 

My customer says he is happy with the 3 measure way that it is displayed, but the only thing I have not accounted for is what happens if an engineer has no missed assignments?

Would there be a divide by 0 error, or would the engineer just show a score of 0?

Thanks for the help!

[Anonymous]

Currently I am using 3 measures, but I am not very confident that this is correct, hence the post:

RTI = CALCULATE( SUM(RoundRobin[Weight.1]), FILTER(RoundRobin, RoundRobin[Attribute] = "RTI Assignments"))

Missed = CALCULATE( SUM(RoundRobin[Weight.1]), FILTER(RoundRobin, RoundRobin[Attribute] = "Missed Assignments"))

Score = DIVIDE([RTI], [Missed])

I then used engineer as the legend for a bar chart, and added a date slicer to the page.