
1. Re: How to COUNTD based on specific criterion?
Mahfooj Khan Sep 28, 2016 2:06 AM (in response to Suvojit Basu)Its because you've already aggregated your [Benchmark Price].
Try this and let me know If you've any query.
IF SUM([Price])< [Benchmark Price] THEN COUNTD([Store ID]) END
Mahfooj

2. Re: How to COUNTD based on specific criterion?
Suvojit Basu Sep 28, 2016 8:00 AM (in response to Mahfooj Khan)Hi Mahfooj,
Thanks very much for responding to my question. Unfortunately the formula you suggested did not work. Just to give you a quick background, I had tried a similar formula myself except I used AVG instead of SUM.
IF AVG([Response (Measure)])<[BM Price (Main for Tbl)] THEN COUNTD([LocationGUID (Measure)]) END... This was my formula. Whether I use AVG or SUM, I think what's happening here is that the formula is being evaluated at an aggregate level. For example (using my table in the original question), for Product B, the average price ($7.99) is less than the benchmark price (avg price of product A  $12.99) and so the formula is simply doing a COUNTD of all stores where product B was audited and showing that number under COLUMN 1 (Table above). What I really want is to count the total number of stores where the price of product B was < the average price of the 'main' product and show the number in COLUMN 1; same logic for COLUMN 2 and 3 in table above.. Your formula behaves in a similar manner and mine shown above.
Just so you know, there is also another version of this table that I've created  the difference here is that the 'benchmark price' is hard coded and part of the data set. In this case, I used this formula there and it works perfectly.
COUNTD (IF [Price] < [Benchmark Price] THEN [Store ID] END)... When I use this calculated field (named #Stores < BM) in my table, it shows as AGG(#Stores < BM) and works fine.
In this new scenario where the 'benchmark price' is dynamic i.e. it is the average price of the 'main' product, I tried to use this formula with a small tweak COUNTD ( IF AVG([Price]) < [Benchmark Price] THEN [Store ID] END ) but it doesn't allow me and throws up the error message saying 'cannot mix aggregate and nonaggregate'.
Sorry for the long reply! Wanted to explain the situation in detail. So the issue still remains unresolved... Please let me know if you have any other ideas.
Thanks a ton!