Sum Column Based on Distinct values in another column

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

Find more posts tagged with

Accepted answers

MarkSnodgrass

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.

All comments

MarkSnodgrass

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.

user18178

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

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