Forum Discussion
Looking for help with confusing grouping/ calculation based on 3 columns
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!
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
4 Replies
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
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.- tackytechtom
Most Valuable Professional
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.instagram.com/tackytechtom
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!
