Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello ! I have a set of data similar like the picture above.
(i) The same PlanID and PlanFrame have different DowntimeReason.
(ii)The same PlanID but different PlanFrame have different DowntimeReason.
I need to sum up all the Timespend and divide with sum of TotalRunTime & TotalDownTime, meaning sum(Timespend)/ (sum(TotalRunTime)+sum(TotalDownTime)). But the difficult part is i need to ignore all the rows where NcQty is negative and at the same time, i can only add the TotalRunTime and TotalDownTime where PlanId and PlanFrame is distinct value. If PlanId is the same but PlanFrame is different, then i need to add up too. So for the picture above, i need to get sum(Timespend) / ((8722+6542+3325+6541+1213+5429) + (799+142+350+450+540+225)).
So anyone have any idea how to write the expression for this? For now, what I did is as below:
=(Sum({$<[NcQty]-={"<0"}>}[Timespend]))
/(Sum({$<[NcQty]-={"<0"}>}[TotalRunTime]) + Sum({$<[NcQty]-={"<0"}>}[TotalDowntime]))
This expression only fulfilled the ignoring NcQty which is negative but doesn't fulfilled the distinct planId and planframe. Can anyone tell me how to do it? Thanks in advance !
So for the picture above, i need to get sum(Timespend) / ((8722+6542+3325+6541+1213+5429) + (799+142+350+450+540+225)).
Firstly, in your post, since NcQty is -7, shouldn't PlanID456 with PlanFrame2 be excluded (in orange)?.
If so, required result: 30603 (total run time = 28447 + total downtime = 2156)
try this:
Create a concatenated key of PlanID&PlanFrame as Key.
expression:
Sum(Aggr(Sum(DISTINCT {$<[NcQty]-={"<0"}>} [TotalRunTime]), Key))
+
Sum(Aggr(Sum(DISTINCT {$<[NcQty]-={"<0"}>} [TotalDownTime]), Key))
Sorry, it's my mistake, the orange number should be excluded. I tried your way and it's brilliant ! I got the visualization that I want. Btw, I have another question. If I wan to get the exact same thing for previous month, how should i type the expression? This is what I did,
sum(aggr(sum(DISTINCT{$<[DateValue.autoCalendar.Month]={"$(=Month(AddMonths(max([DateValue]),-1)))"},[NcQty]-={"<0"}>}[TotalRunTime]),[Key]))
+
sum(aggr(sum(DISTINCT{$<[DateValue.autoCalendar.Month]={"$(=Month(AddMonths(max([DateValue]),-1)))"},[NcQty]-={"<0"}>}[TotalDowntime]),[Key]))
The graph didn't show anything, but previously without the Distinct issue, I used the same expression and the visualization that I got for previous month is correct. Is it that i cannot combine both the previous month and NcQty condition in set analysis like what i did?