Is there a way to count totals of unique combos within a pivot?
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.
Answers
-
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.
0 -
@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.
2 -
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?
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1
Categories
- 10.5K All Categories
- 4 Connect
- 914 Connectors
- 250 Workbench
- 458 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 185 Visualize
- 250 Beast Mode
- 2.1K Charting
- 11 Variables
- 16 Automate
- 354 APIs & Domo Developer
- 88 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 245 Distribute
- 62 Domo Everywhere
- 242 Scheduled Reports
- 20 Manage
- 41 Governance & Security
- 170 Product Ideas
- 1.2K Ideas Exchange
- 10 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive