Is there a way to count totals of unique combos within a pivot?

slesty
slesty Member
edited March 2023 in Datasets

I have combined a few documents that have created duplicative shipping bill amounts due to unique identifiers that were part of the same shipment. I need to keep the rest of the data in rows that contain duplicative shipping bill totals but can't get a SUM of the UNIQUE bills. One extra complexity is that there are 10k+ bill amounts and some are the same amounts. I can make any suggested tweaks in ETL as well. I have an example of the data below:

Customer Service # Service Revenue Ship Invoice # Ship Invoice $

A 1 $15 1A $10

B 2 $17 2A $11

C 3 $18 3A $12

D 4 $16 1A $10 (This is where the double counting happens --> pivot says total ship invoice is $20) Customer D's package was included in Customer A's because they are in the same office.