Flexible SUM Aggregates With Duplicate Values in Dataset
Hello everyone.
I have a data set that looks like this:
This data set shows product purchases at the customer level. Thus, product IDs are duplicated in the data set.
Here's the challenge.
I want to show the total value of products, counting each product value only once in a SUM. I've added a Product Rank column as a helper, so that the SUM formula can look like this:
SUM(CASE WHEN Product Rank = 1 then Price END)
The issue is when I add a filter to the dashboard for Customer Type (say, for "In Store"), then that SUM formula will not capture the total value because Product Rank = 2.
It seems what I'd need to do is run my rank and window on the fly based on the filters that are selected. Essentially, the issue is needing a way to de-duplicate the values that contribute towards the SUM calculation.
Best Answer
-
@jmmc you can do this with SUM(DISTINCT ())
SUM(DISTINCT Price) FIXED (BY Product ID)
Yields the following results. You can see that the column fixed1 only includes the total once per id.
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**2
Answers
-
@jmmc you can do this with SUM(DISTINCT ())
SUM(DISTINCT Price) FIXED (BY Product ID)
Yields the following results. You can see that the column fixed1 only includes the total once per id.
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**2 -
I am confused on how these are duplicates. Are they not separate transactions? For example, one online and one in store in the sample data above? If you want to see total value, why would you want to exclude customers?
If I solved your problem, please select "yes" above
0 -
@david_cunningham this is excatly what I was looking for, this is a very powerful feature. Thank you!
0
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 303 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 104 SQL DataFlows
- 637 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 763 Beast Mode
- 65 App Studio
- 42 Variables
- 709 Automate
- 184 Apps
- 460 APIs & Domo Developer
- 54 Workflows
- 11 DomoAI
- 39 Predict
- 16 Jupyter Workspaces
- 23 R & Python Tiles
- 401 Distribute
- 116 Domo Everywhere
- 277 Scheduled Reports
- 8 Software Integrations
- 134 Manage
- 131 Governance & Security
- 8 Domo Community Gallery
- 44 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 111 Community Announcements
- 4.8K Archive