Sum by using unique values in a different column

Reply
Visitor

Sum by using unique values in a different column

Hi there,

 

I'm kinda stuck here, I've read other similar threads on this topic but I don't think any of the solutions applies to me because I'm using the global filters on the page quite a bit here.

 

Below is an example data set

 

Activity IDActivity CategoryEmployeeOrder IDOrder Amount
AAAEventsJohn1235000
BBBEventsSusan1235000
CCCEventsSusan1235000
DDDWebinarJohn1235000
EEEWebinarSusan1235000
FFFWebinarSusan4562000
GGGEmailJohn4562000
HHHEmailJohn4562000

 

What I'm looking to do is some sort of formula that allows me to sum the Order Amount based on the unique Order ID's. So the output for this table would be 7000.

 

However, the trick here is that I need to be able to do this calculation while filtering the Activity Category and/or Employee on the domo page. 

 

For example, if I choose to filter Activity Category = Webinar, the data set would look like this, and the Total should be 7000

 

Activity IDActivity CategoryEmployeeOrder IDOrder Amount
DDDWebinarJohn1235000
EEEWebinarSusan1235000
FFFWebinarSusan456

2000

 

But if I filter Activity Category = Events, the data set would look at this, and the Total should be 5000

 

Activity IDActivity CategoryEmployeeOrder IDOrder Amount
AAAEventsJohn1235000
BBBEventsSusan1235000
CCCEventsSusan1235000

 

Last example, if I were to filter by Activity Category = Events and Webinar, and then filter Employee by Susan, the data set would look at this and the Total should be 7000

 

Activity IDActivity CategoryEmployeeOrder IDOrder Amount
BBBEventsSusan1235000
CCCEventsSusan1235000
EEEWebinarSusan1235000
FFFWebinarSusan4562000

 

The solutions I've seen for this mostly don't allow for this level of filtering while still calculating the correct total based on unique Order ID so I'm hoping there's a method/workaround for this that I'm not aware of.

 

Thanks

Black Belt

Re: Sum by using unique values in a different column

Do you have access to the dashboard layout feature yet?

 

If you do, then you can accomplish this with a few interactive filter cards.

 

Let me know if you have trouble viewing the video and I will work on some screenshots instead


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
Visitor

Re: Sum by using unique values in a different column

Oh amazing, yes I can see the video and yes I have access to the Dashboard layout feature. Can you elaborate a bit more on how you're able to have a sum by unique order ID?

 

Did you have to do anything special with the data set or the cards? Or is this something that only a pie chart can do?

 

Thanks,

 

Highlighted
Green Belt

Re: Sum by using unique values in a different column

That looks great!  I wonder when our instance will have that feature?

Announcements
We want your feedback to improve the Dojo Community! If you are coming to the Dojo Award Ceremony next week Click here to learn more!