3 Replies Latest reply on Jan 17, 2019 12:18 AM by pavan hm

    Distinct count with IF Condition

    Data Modeler

      Hi there! I am trying to do a distinct count over 'customer ids' whenever the 'usage' is greater than 10%. I am using INCLUDE because I also want a breakdown of Month which is in my view.

       

      The following formula gives me the customer count but it is not a distinct count (because there are duplicate customer ids in data):

      {INCLUDE [customer_id]: SUM(IF [Usage]>=0.10 THEN 1 ELSE 0 END)}

       

      Now, to get to a distinct count, I am doing:

      {INCLUDE [customer_id]: COUNTD(IF [Usage]>=0.10 THEN 1 ELSE 0 END)}

      This not yielding correct results.

       

      I tried: {INCLUDE [customer_id]: COUNTD([Usage]>=0.10)}

      but this is also not providing correct results.

       

      Any ideas. Thank you. Highly appreciate the help!