Count Distinct Collect - Unique Values Several Columns - Help
Hello!
I am trying to get the unique number of countries that have a value greater than 0 from a set of columns, the formula works, if I just collect from one column, example below, I get what I want, but if I try to add Carlos's Colum or others to the formula, then it does not work or just returns 0 or 1.
=COUNT(DISTINCT(COLLECT(Countries:Countries, [Melissa's Moves]:[Melissa's Moves], >0)))
The above is the one that works, if I try any of the below, it fails
=COUNT(DISTINCT(COLLECT(Countries:Countries, [Melissa's Moves]:[Melissa's Moves], >0, Countries:Countries, [Carlos's Moves]:[Carlos's Moves], >0)))
=COUNT(DISTINCT(COLLECT(Countries:Countries, [Melissa's Moves]:[Melissa's Moves], Countries:Countries, [Carlos's Moves]:[Carlos's Moves], >0)))
I am trying to show the total unique number of countries for which there are active moves greater than 0 in each column
Any ideas on what might be the correct formula?
Thanks!
Best Answer
-
You would need to insert a helper column and have the row's total on each. Then you would be able to reference the helper column and the country column.
Answers
-
I kept playing around and I am closer but now with the challenge that I think I might have duplicates
COUNTM(COLLECT(Countries:Countries, [Melissa's Moves]:[Melissa's Moves], >0)) + COUNTM(COLLECT(Countries:Countries, [Carlos's Moves]:[Carlos's Moves], >0))
It gives also what I need but I need to find a way to only count unique between the two
-
Lord have mercy.... I was able to do it with a report lol
If anyone knows how to get a formula to do this I would be interested in learning, thank you!
-
You would need to insert a helper column and have the row's total on each. Then you would be able to reference the helper column and the country column.
-
@Paul Newcome monuments should be raised in your honor! You are the best! I had the totals already and I didn't think of doing that.
THANK YOU!
-
Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!