How do I group (summarize) only certain data?

ucstevenfu
ucstevenfu Member
in Magic ETL

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

  • quinnj
    quinnj Contributor
    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.

Answers

  • kshah008
    kshah008 Contributor

    Hi all,

     

    Can anybody help @ucstevenfu with their question?

    Thanks!

  • quinnj
    quinnj Contributor

    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
    ucstevenfu Member

    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
    quinnj Contributor
    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.

  • ucstevenfu
    ucstevenfu Member

    That works perfectly. Thanks!

  • saipawar
    saipawar Member

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

  • serra
    serra Member

    Thanks!

  • RomanC
    RomanC Member

    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?

Categories