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 ID | Activity Category | Employee | Order ID | Order Amount |
| AAA | Events | John | 123 | 5000 |
| BBB | Events | Susan | 123 | 5000 |
| CCC | Events | Susan | 123 | 5000 |
| DDD | Webinar | John | 123 | 5000 |
| EEE | Webinar | Susan | 123 | 5000 |
| FFF | Webinar | Susan | 456 | 2000 |
| GGG | Email | John | 456 | 2000 |
| HHH | Email | John | 456 | 2000 |
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 ID | Activity Category | Employee | Order ID | Order Amount |
| DDD | Webinar | John | 123 | 5000 |
| EEE | Webinar | Susan | 123 | 5000 |
| FFF | Webinar | Susan | 456 | 2000 |
But if I filter Activity Category = Events, the data set would look at this, and the Total should be 5000
| Activity ID | Activity Category | Employee | Order ID | Order Amount |
| AAA | Events | John | 123 | 5000 |
| BBB | Events | Susan | 123 | 5000 |
| CCC | Events | Susan | 123 | 5000 |
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 ID | Activity Category | Employee | Order ID | Order Amount |
| BBB | Events | Susan | 123 | 5000 |
| CCC | Events | Susan | 123 | 5000 |
| EEE | Webinar | Susan | 123 | 5000 |
| FFF | Webinar | Susan | 456 | 2000 |
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
