Sum only first entry

[KriZo]

Hi

I have been trying to figure this out for a few days now. I am very new to DAX language, but pretty good at googeling, and figuring out how to change what ever functions i might find, but this time i haven't succeeded. 

I have a bunch of columns and rows that contains arrival of items from suppliers. in a new colum i have created a formula that returns the value 1 if the line was delivered timely, and 0 if it wasn't, column named [On time]. However as it often occurs, a supplier might deliver a line partially. What i want is to sum only the values, for the first entries. 

 Each row has an orderline ID which is a concatanation of order No, Orderline no, Item. 

So i need a Dax formula that checks whether the [Orderline ID] exists more than once, and if it does, only return the value from [On Time], from the first time this orderline was delivered. [Orderline Arrival Date]. 

Hope it makes sense 🙂 

[v-ljerr-msft]

Hi @KriZo,

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. 

Regards

[KriZo]

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?

[v-ljerr-msft]

Hi @KriZo,

---

---

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? 

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

[KriZo]

@v-ljerr-msft

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%

[KriZo]

Nevermind mind second part. Solved it with 

SUMX(DelOrder;[OTP]/DISTINCTCOUNT(DelOrder[OrderLineID]))

[KriZo]

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%

[KriZo · Accepted solution]

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. 

[KriZo]

I think showing with this table might make more sense. 

I need to Power BI, to basically exclude all rows, that are not the first time arrival and then sum the On Time Value. 

So in this case, there are 2 orderlines, each line has been partially delivered 3 times. Some of them was on time, some where not. But i only want it to sum the On Time values from the first delivery of an order line. 

[ricardocamargos]

Hi @KriZo,

Can you provide a dataset as sample ?

Ricardo