Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Sum only first entry

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...
  • Anonymous's avatar
    Anonymous
    8 years ago

    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. 

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
8 years ago

Hi 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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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%

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Nevermind mind second part. Solved it with 

         

        SUMX(DelOrder;[OTP]/DISTINCTCOUNT(DelOrder[OrderLineID]))
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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.