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.
Microsoft EmployeeHi Anonymous,
Could you post your table structures with some sample/mock data and the expected result, so that we can better assist on this issue? It's better to share a dummy pbix file which can reproduce the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. :smileyhappy:
Regards
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?