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

slesty

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.

MichelleH

@slesty To be sure I completely understand your question, what are you expecting the Ship Invoice total to be in your double counting example? Should it be $10 for Customer A and $10 for Customer D?

slesty

The Ship Invoice total should be $10 total for that shipment - Ideally, I would be able to filter by Ship Invoice and only see $10. I can currently cut it at the customer level and have individual data, but the other pivot outputs are double counting that invoice for example.

MichelleH

@slesty If you want to see the the totals by shipment, then I recommend creating a separate output to your ETL to remove all customer-related fields and remove duplicates so have a single row per shipment. Then you would use that output for cards to aggregate the invoice amounts without double-counting, and use your original dataset for customer-specific reporting.

ST_Superman

I agree with @MichelleH . If you have a single shipment going to multiple customers and need to be able to show the value of that shipment at a customer level, then you will be double counting if you use the same data to view the data at a shipment level.

Is there a reason that this needs to be on a single dataset?