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!

Find more posts tagged with

Accepted answers

quinnj

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.

All comments

kshah008

Hi all,

Can anybody help @ucstevenfu with their question?

Thanks!

quinnj

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!

ucstevenfu

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.

quinnj

Ah, I see. Yes, that certainly makes sense.

ucstevenfu

That works perfectly. Thanks!

saipawar

@ucstevenfu Can you share the code for max/min that worked for you?

serra

Thanks!

RomanC

That makes sense @quinnj , but what if you want to some up the account values, so in this case, you want 335 as a value of those two accounts combined, how would you do that?