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.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.2K Connectors
- 284 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.9K Transform
- 88 SQL DataFlows
- 554 Datasets
- 2.2K Magic ETL
- 3.3K Visualize
- 2.3K Charting
- 561 Beast Mode
- 9 App Studio
- 27 Variables
- 577 Automate
- 140 Apps
- 414 APIs & Domo Developer
- 22 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 350 Distribute
- 90 Domo Everywhere
- 258 Scheduled Reports
- 2 Software Integrations
- 91 Manage
- 88 Governance & Security
- 9 Product Release Questions
- Community Forums
- 42 Getting Started
- 28 Community Member Introductions
- 85 Community Announcements
- 4.8K Archive