count if sum if from a matrix

[Deekey]

Hello, I got a question..

the table is as below
ProductType                User                 Amount         
TypeA                              Alice                 $10
TypeA                              Alice                 $10
TypeA                             Billy                   $10
TypeA                             Cathy                $10
TypeA                             David                $10
TypeB                              Alice                 $10
TypeB                             Billy                   $10
TypeC                             Billy                   $10
TypeC                             Cathy                $20

I would like to calculate : no. of User who spent exact $10 in each ProductType

Expected result as below:
ProductType                No.of User($10)
TypeA                                     3
TypeB                                     2
TypeC                                     1

I try to make a Table, the column was put  the ProductType, and add the measure as :

Count(DISTINCT {<User={"=Sum(Amount)=10"} >} User)

But seem not work... anyone can advise.. thanks :@

[GaryGiles · Accepted solution]

You can use the Aggr() function to test the sum of Amount for each ProductType & User like this:

=Count(Distinct if(aggr(sum(Amount), ProductType,User)=10,User))

[JuanGerardo]

Hi @Deekey, I think you are looking for:

Count({<Amount={10}>} Distinct User)

JG

[Deekey]

Done. Thanks a lot 🙂