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

# How to count the number of distinct records?

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?

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?

Hi,

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

Mahfooj