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

    Counting Distinct Values in CASE statement

    James Bostock

      All,

       

      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?

       

      Thanks

       

      James