Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a set analysis formula counting Sales from “Germany” as follows:
(Count({<[Country]={‘Germany}>} Sales))
I would like to however only consider the uniques of a third column called “Submissions”. The current selection double counts Submissions.
How might I go about removing all duplicates of a third column / submissions column?
Generally, when something is counted multiple times, there's an issue in the data structure that needs to be addressed (often two tables that are joined into one table but should not be resulting in duplication). However, if the specific issue is that one column is always counted twice, it'd be easier to just divide the result by two. You could also count another field, e.g. count(distinct SalesID), if one exists. Finally, you could try using something like:
Count(aggr(Only(Sales),SalesID))
Where SalesID is a field (or a combination of fields separated by a comma) that designate individual rows.
However, as I led off with, it's usually best to fix the data source to prevent duplication in the first place instead of trying to work around it within the formulas.