2 Replies Latest reply on Jul 10, 2016 10:05 PM by Mahfooj Khan

    How to count the number of distinct records?

    Jung Jein

      Say I have the following dataset.

      Customer IDCountrySale AmountProfit Amount
      1US100200
      1TH100200
      2US100200
      3CN100200
      3CN100200
      3IN100200
      4US100200
      4TH200
      4CN100200
      5CN100

       

      And I sum it to get this.

      CountrySale AmountProfit Amount
      US300600
      CN300600
      TH200400
      IN100200

       

      I want to find the number of non_null unique customer ID in each country_saleamount and country_profitamount cell. Ideally, I want to get this so I would like to know what kind of calculation to use:

      CountryNumber of Customer with Sale Amount
      Number of Customer with Profit Amount
      US33
      CN32
      TH12
      IN11
        • 1. Re: How to count the number of distinct records?
          Tina Hauser

          Hi Jung!

          One way to do this is shown the attached workbook.

           

          First, create a couple of boolean calculated fields to determine where non-null sales and profit data exists. These are Dimensions in the example I've attached, named "Profit Amt Exists?" and "Sales Amt Exists?". I've used a level of detail calculation to keep this unique to each country/customer ID:

          { FIXED [Country],[Customer ID]:zn(SUM([Profit Amt])) > 0

           

          Then, create your measures, named "Customers with Profit Amt" and "Customers with Sale Amt" in the example. These also use level of detail to count the distinct number of customer IDs:

          IF [Profit Amt Exists?] THEN {FIXED [Country],[Customer ID]:COUNTD([Customer ID])} END

           

          Let me know if you have any questions.

          Thanks!

          Tina

          • 2. Re: How to count the number of distinct records?
            Mahfooj Khan

            Hi,

             

            you can also do this in a single LOD. See the screen shots

            Mahfooj