2 Replies Latest reply on Aug 20, 2018 1:51 PM by Eitan Boral

    Create Custom Calculation - Unique Metric by Unique Metric

    Eitan Boral

      I have Customer ID, Product ID, and Industry columns. For example:

       

       

      CustomerIDProductIDIndustry
      1AEntertainment
      1BEntertainment
      2AEntertainment
      3AEntertainment
      4AEntertainment
      4BEntertainment
      4ACleaning

       

      I'd like to set up a grid with industry for my columns and Product ID for my rows where I'm trying to show the % of customers in a specific industry purchasing each product.

       

      In other words the (number of unique customers by Product ID)/(Unique customers in each industry).

      The numerator is: COUNTD([Customer ID]) The denominator is throwing me off. I'm not sure how to get unique customers in an industry.

      In the example above, product A has 4 unique customers in the Entertainment industry, and Entertainment has 4 unique customers for all products, so the calculation for product A would 4/4 or 100%.

      But product B would be 2 unique customers / 4 total unique customers in Entertainment or 50%.

      Does anyone know how I create the calculation for the denominator.

      COUNTD([Customer ID])/???