2 Replies Latest reply on Jul 30, 2013 9:48 AM by James Bostock

    Counting Distinct Values in CASE statement

    James Bostock



      Hoping some of the tableau gurus can help here. I've 2 tables, one with a list of unique values (opportunities) and one with a linking value that can appear multiple time (it's a product table being linked on Opportunity ID). The tables are in the same DB and are left joined.


      I'm trying to find out the # of wins which is defined using 2 criteria (sales stage = closed, prob=100%). The problem is that when I created the below calculated field it is counting all the products too.


      CASE [Sales Stage] WHEN '02 - Sale Closed' THEN



          CASE [Prob %] WHEN 100 THEN 1 ELSE 0 END



      ELSE 0 END


      Ideally I'd like to insert a COUNTD into the CASE statement, but I can't mix agg/non-agg so I'm a little stuck.


      Any ideas?