How do I group (summarize) only certain data?
Hi,
I have Salesforce data that comes from a couple of different objects. Primary is Opportunities but it's also pulling some information from Accounts. The data would look like this:
Account Name Opportunity Opportunity Value Account Value
John Doe Opp 1 50 125
John Doe Opp 2 65 125
John Doe Opp 3 30 125
Jane Smith Opp 1 25 210
Jane Smith Opp 2 405 210
What I want to do is build a grouped bar chart that will compare the sum of the opportunity value vs. the sum of the account value PER ACCOUNT NAME. So in other words, the comparison would be as such:
Account Name Opportunity Value Account Value
John Doe 145 125
Jane Smith 430 210
However, I'd want to drill down on an adviser to see the specific opportunity details. So if I clicked on John Doe in the grouped bar chart I would then see the 3 opportunities that comprise that are tied to his account.
Is there a beast mode or dataflow transform that can handle this?
Thanks!
Best Answer
-
Ah, I see. Yes, that certainly makes sense.
One option then would be to change how we aggregate the "Account Value". Once we add it as a metric column to our card, instead of using "SUM", we could use "MIN" or "MAX". We're not actually trying to get a minimum or maximum value, because all the "Account Value"s should be the exact same if we group by Account (i.e. use the Account Name as our x-axis variable), but using MAX or MIN will give us just the single value of the Account.
2
Answers
-
0
-
Hey @ucstevenfu,
I actually don't think you need to do any kind of transform on the data, if I understand what you're looking for correctly.
At the card-level itself, we have the ability to do just the kind of aggregation you're talking about. For example, you could create a simple bar chart and put the "Account Name" as the x-axis, and then add two metric columns, one for "Opportunity Value" and one for "Account Value". When you add those columns to the card, you're given the option to aggregate them and you can choose "SUM". This will give you the sum of each metric by Account Name.
You can then add a drillpath card (from the card detail view, click the edit wrench in the top right, select "Edit Drillpath", then select "Add a view"), and replace the "Account Name" on the x-axis with the "Opportunity".
Hope that helps!
1 -
Thanks for the response @quinnj
If I were to do that it would sum up both the Account Value and the Opportuntiy Value for each Account Name. However, the Account Value is actually a single value per Account Name, but because there are multiple Opportunity Values associated with each Account Name, the Account Value is duplicated per record.
This happens when I pull Salesforce data (using Domo's built-in interface) from the Opportunity table but tie in data from the Account table.
0 -
Ah, I see. Yes, that certainly makes sense.
One option then would be to change how we aggregate the "Account Value". Once we add it as a metric column to our card, instead of using "SUM", we could use "MIN" or "MAX". We're not actually trying to get a minimum or maximum value, because all the "Account Value"s should be the exact same if we group by Account (i.e. use the Account Name as our x-axis variable), but using MAX or MIN will give us just the single value of the Account.
2 -
That works perfectly. Thanks!
1 -
@ucstevenfu Can you share the code for max/min that worked for you?
0 -
Thanks!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive