
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Count distinct with IF/Filter formula on measure
Hi,
I'm quite new to QS and I have a question on a calculation I want to make.
I want to count the number of distinct customers that got a value of a certain category.
Example data in my pivot:
Person | Fee | Other |
An | 100 | 10 |
An | 350 | 0 |
Ben | 0 | 100 |
So I want to count:
Fee | Other |
1 (person) | 2 |
Can you help me identify a good formula for this case?
PS: I found a couple of similar cases, but either they were to complicated or the provided answer did not work in my case.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Tienemientje , What did you try, and how was it not working as you expected? With a 'certain category', do you mean within a given interval or a certain value?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
With the category, I mean like in my example "fee" and "other".
I tried a number of variations on the following expression
Count (distinct [Name] and [Fees]>0)
It either gives an error, like if Y try to add the IF statement it says that it needs more parts. (which I can understand, similar to excel, but I found examples online where they did not seem to add the "if not" part). Or it gives a zero or just some high number (either not the distinct values or not only those names for which the fee is above 0)
I now received something from a colleague, but I need to check further if it gives exactly the values I want
Count(distinct{<[name]={"=sum([fee]>0"}>}[name])

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Will
count({<Fee={">0"}>}distinct Person) and
count({<Other={">0"}>}distinct Person)
give you the numbers that you want, or do you somehow need to consider the sums per person? (asking because of your collegue's suggestion)
