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.
Sure thing, here is an example.
https://www.dropbox.com/s/orvdzha2wi36htq/Supplier%20Performance%20Example.pbix?dl=0
I actually managed to solve it my self, in a way. In the Query editor I sort the arrival date by so the earliest is in the beginning. And then i use this formula:
OTP = SUMX(DISTINCT(DelOrder[OrderLineID]);FIRSTNONBLANK(DelOrder[On Time];0))/DISTINCTCOUNT(DelOrder[OrderLineID])
Now it only sums the earliest On Time value, of a delivered orderline. , this works, but maybe there is a different solution, in case i hadn't sorted the data?
Hi Anonymous,
Now it only sums the earliest On Time value, of a delivered orderline. , this works, but maybe there is a different solution, in case i hadn't sorted the data?
Could you try using the formula below to see if it works in this scenario? :smileyhappy:
OTP2 =
SUMX (
DISTINCT ( DelOrder[OrderLineID] );
VAR firstArrivalDate =
CALCULATE ( MIN ( DelOrder[Orderline Arrival Date] ) )
RETURN
CALCULATE (
SUM ( DelOrder[On Time] );
FILTER ( DelOrder; DelOrder[Orderline Arrival Date] = firstArrivalDate )
)
)
/ DISTINCTCOUNT ( DelOrder[OrderLineID] )
Regards
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%