First occurrence column

[awitt]

I have a dataset which has multiple records per tracking number. These record coincide with different surcharges related to shipping. On each record there is a column titled "numberofparcels" in which the total number of parcels is included. However when consolidate the data, those number of parcels are being added together when in reality it's just the first occurrence of that figure that should be calculated. 

The data below shows 16 parcels for the tracking number ending in 3338 and 288 parcels for the tracking number ending in 8420. In reality tho, this should just be 2 and 9 respectively. I would like to have a column where the first instance shows the number of parcels but all other occurances of that tracking number are blank. I've tried different countrows functions (which is not ideal since my actual dataset has over 5m records) and nothing seems to work. Sample data is below. 

Sample PBIX file.  

[Anonymous · Accepted solution]

HI @awitt,

I'd like to suggest you write a measure to add a variable with summarize function to summary records and remove duplicate records based on current 'SintSchoolID', 'Tracking number' groups. Then you can use the iterator function to summary these unique values.

measure =
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( Table ),
        [SintSchoolID],
        [Tracking number],
        "numberofparcels", MIN ( [numberofparcels] )
    )
RETURN
    SUMX ( summary, [numberofparcels] )

If you want a calculated column version, did these any unique field or DateTime values include in your table? If that is the case, you can use them as an index to choose which remains and others to replace with blank.

Notice: the current power bi data mode table does not include column and row index, so you need to use another field that has sortable and unique values as the index.

Regards,

Xiaoxin Sheng

[amitchandak]

@awitt , Try measure like

sumx(Summarize(Table, Table[tracking number], Table[numberofparcels]),[numberofparcels])

or

sumx(Summarize(Table, Table[tracking number], "_parcel" , max(Table[numberofparcels])),[_parcel])