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
- All Categories
- 1.1K Product Ideas
- 1.1K Ideas Exchange
- 1.2K Connect
- 969 Connectors
- 256 Workbench
- Cloud Amplifier
- 1 Federated
- 2.4K Transform
- 76 SQL DataFlows
- 500 Datasets
- 1.8K Magic ETL
- 2.7K Visualize
- 2.2K Charting
- 368 Beast Mode
- 19 Variables
- 483 Automate
- 101 Apps
- 378 APIs & Domo Developer
- 6 Workflows
- 22 Predict
- 6 Jupyter Workspaces
- 16 R & Python Tiles
- 316 Distribute
- 64 Domo Everywhere
- 252 Scheduled Reports
- 59 Manage
- 59 Governance & Security
- 1 Product Release Questions
- 5K Community Forums
- 37 Getting Started
- 23 Community Member Introductions
- 63 Community Announcements
- 4.8K Archive