Sum Column Based on Distinct values in another column

user18178
user18178 Member
in Magic ETL

I need to sum the quantity in one column based off the unique values of another column. I cannot remove the duplicates within the ETL because there are other columns with greater detail that would be lost if done.  So i need a beast mode that would give me a result of 50 for the sample data below.  Any help is appreciated. 

 

DOMO question.PNG

 

 

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    I would agree that the ETL would be the way to go. I would suggest that you look into the Rank and Window options in the ETL. Here's a knowledgebase article that breaks it down. 

    https://knowledge.domo.com/Prepare/Magic_Transforms/ETL_DataFlows/03ETL_Actions%3A_Rank_and_Window

    Also, it is worth noting that you can have one dataset that powers your single value card and then another dataset that powers the drill path card that would be displayed when you click on the single value. This would be helpful for you if you need to create an ETL that gets the single value number that you are wanting, but doesn't show all the detail that you are wanting as well. Basically, you would have a summary ETL and detail ETL and use the summary ETL for the single value card and the detail ETL for the card type that you choose for the detailed information.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    qty.PNG

    If you just have the line column and the quantity column and choose SUM as your aggregation, you should get what you are looking for. If you click on a line it will drill into that line and show you the details of the line number. 

    Now, if you add other columns into your table card, it will start taking the uniqueness into account and not sum the quantity by how you are wanting.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • user18178
    user18178 Member

    Thank you for your response but ultimately i will not be using a table card.  I will be using a single value gauge card. And for the result I would be looking for a result of 50.  So Line #1 shows up 3 times with a value of 5 but I only want one instance of the 5 to be included in my sum. Same goes for each of the other line numbers.  I want a beast mode that would read the data and do 5+10+25+10.

     

    I am thinking a unique column in my ETL dataflow needs to be created to single out only one instance of each line # and then I can sum.  Other than that I do not know how to get the result I am needing. 

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    I would agree that the ETL would be the way to go. I would suggest that you look into the Rank and Window options in the ETL. Here's a knowledgebase article that breaks it down. 

    https://knowledge.domo.com/Prepare/Magic_Transforms/ETL_DataFlows/03ETL_Actions%3A_Rank_and_Window

    Also, it is worth noting that you can have one dataset that powers your single value card and then another dataset that powers the drill path card that would be displayed when you click on the single value. This would be helpful for you if you need to create an ETL that gets the single value number that you are wanting, but doesn't show all the detail that you are wanting as well. Basically, you would have a summary ETL and detail ETL and use the summary ETL for the single value card and the detail ETL for the card type that you choose for the detailed information.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Categories