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
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 non-aggregate'.
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!