Summing unique values based on a separate column
Having issue trying to sum values that have a one to many relationship without over-counting the value.
I have marketing events that I'm trying to attribute opportunity created from and get the overall ROI while maintaining drill down functionality within the dataset itself. This is leading to marketing events that resulted in multiple opportunities being over counted when i sum up the marketing event costs.
Is there a way to sum the distinct values of my marketing events based on the marketing ID? Using the sum(distinct (EVENT COST)) formula doesn't work unless every marketing event has a different cost, otherwise I'll actually undercount marketing costs when calculating ROI.
Is the only way to get around this to sum up the value of opportunity created by event with no granularity as a separate data flow and use this as the first layer of any card and a more granular data set for drill downs? I'd rather avoid something like this if possible.
Best Answer
-
Hi.
Add "EventSequenceNumber" as DataFlow as below.
MarketingID EventID EventSequenceNumber EventCost m01 e011 1 10000 m01 e021 2 10000 m01 e022 3 10000 m02 e100 1 30000 m02 e300 2 30000 m02 e500 3 30000 BEAST MODE: MarketingCost
sum(
case
when `EventSequenceNumber` = 1 then `EventCost`
else 0
end
)2
Answers
-
Any chance you could provide a snippet of your data? I'm not sure exactly what you are looking for, and it may help provide some suggestions.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
Hi.
Add "EventSequenceNumber" as DataFlow as below.
MarketingID EventID EventSequenceNumber EventCost m01 e011 1 10000 m01 e021 2 10000 m01 e022 3 10000 m02 e100 1 30000 m02 e300 2 30000 m02 e500 3 30000 BEAST MODE: MarketingCost
sum(
case
when `EventSequenceNumber` = 1 then `EventCost`
else 0
end
)2 -
Hi, thanks for the solution, it should work for me too.
But could you please tell me how to add that "EventSequenceNumber" column using dataflow?
Thanks!
0 -
In Tomo's example they are doing a row_number() over the marketing_id column (this can be done in SQL or Magic ETL, but given what you described, I don't think that's what you're looking for. @ST_-Superman-_ said it best, for problems like this, a sample of data would be most beneficial.
if i had to guess, you have two sets of data, event_costs, and event_attendees, and you're trying to ascertain cost per attendee by event. or something along those lines.
instead of JOIN'ing the data together, APPEND / UNION the data together, and then use a beast mode to construct the math.
sum(Cost) / COUNT(Attendees). You can now solve your question without any DISTINCT shenanigans.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
Hi. I have a similar case but I'm not sure how to handle this. Scenario is something like this:
Let's say I want to build 2 cards using Tomo's sample data table (assuming all data is for 1 month):
1) Single Value card showing Total EventCost for the month (10,000 + 30,000 = 40,000) since we are assuming other rows are duplicate; and
2) Filter card (where one can select either MarketingID or EventID).
If I filter the EventID Filter card = 'e011', the Total EventCost in the single value card should show 10,000.
If I filter the EventID Filter card = 'e011' AND 'e021', the Total EventCost is still 10,000.
If I don't have anything selected in the filter card, Total EventCost should show 40,000.
Is this possible?
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 293 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive