Forum Discussion
Sum only first entry
Sorry for all my posts, i promise this is the last :) Just wanted to show, what it ended up being. The solution provided by v-ljerr-msft kept on making issues for me, so i reverted back to the firstnonblank solution. And this was the final, which does excactly what i want everywhere.
OTP = CALCULATE(SUMX(DISTINCT(DelOrder[OrderLineID]);FIRSTNONBLANK(DelOrder[On Time];0))/DISTINCTCOUNT(DelOrder[OrderLineID]);DISTINCT(DelOrder[OrderLineID]);ALL(DelOrder[Orderline arrival date]))
Never the less, thanks for the help. v-ljerr-msft now i know how to use VAR, and i kickstarted som though processes, that eventually lead to my final solution.
It works perfectly, thanks!
One last request, while playing around with it, I noticed that if i only mark 2018, as the year i am interessted in looking at, the OTP is 0.5. As orderline 2510Bike was partially delivered first time in 2017 and 2018. I need it to clear the arrival date filter, forgot to say that. I assume i need to use the All () function on [OrderLine Arrival Date], but i am not sure where to apply it, in your function.
And secondly there is an issue with a wrong grand total. If i have this table for instance.
The grand total should be 300%/3 unique orderlineID = 100%, but it returns 166,67%
Nevermind mind second part. Solved it with
SUMX(DelOrder;[OTP]/DISTINCTCOUNT(DelOrder[OrderLineID]))
Hmm I was a bit too fast. My total is still incorrect.
As you can se here, the OTP result is 133,33%, it should be 66,67%.
(100+100+0)/3=66,67%
Sorry for all my posts, i promise this is the last :) Just wanted to show, what it ended up being. The solution provided by v-ljerr-msft kept on making issues for me, so i reverted back to the firstnonblank solution. And this was the final, which does excactly what i want everywhere.
OTP = CALCULATE(SUMX(DISTINCT(DelOrder[OrderLineID]);FIRSTNONBLANK(DelOrder[On Time];0))/DISTINCTCOUNT(DelOrder[OrderLineID]);DISTINCT(DelOrder[OrderLineID]);ALL(DelOrder[Orderline arrival date]))
Never the less, thanks for the help. v-ljerr-msft now i know how to use VAR, and i kickstarted som though processes, that eventually lead to my final solution.