First instance value - second instance 0

[awitt]

I have orders data that can feature several shipments per one order. Inisde those split shipments could be multiple items and the orders data has a different line for each item. I am looking to return the two columns highlighted below.

The first column is capturing the first instance of a unique OrderID / Tracking field and returning the cost associate while the other instances of that same value are assigned a "0". The second column is doing basically the same thing only counting the first intance and 0ing out the others. I will be eventually summing the column values or finding column averages for different analysis. 

[TeigeGao · Accepted solution]

Hi @awitt ,

Please refer to the following DAX query:

Shipping Cost per order =
IF (
    'Table'[Item]
        = CALCULATE (
            MIN ( 'Table'[Item] ),
            TOPN (
                1,
                FILTER ( ALL ( 'Table' ), 'Table'[Tracking] = EARLIER ( 'Table'[Tracking] ) ),
                'Table'[Item], ASC
            )
        ),
    'Table'[Cost],
    0
)

Number of Ships Per order =
IF ( 'Table'[Shipping Cost per order] <> 0, 1, 0 )

The result will like below:

Best Regards,

Teige