Use Distinct Count In Excel On Mac

To get the distinct count in the Pivot Table, follow the below steps: Right-click on any cell in the ‘Count of Sales Rep’ column. Click on Value Field Settings In the Value Field Settings dialog box, select ‘Distinct Count’ as the type of calculation (you may have to scroll down the list to find it). The table is filtered with 'Finland' and 'Mexico' in column C, the following array formula counts unique distinct values based on a filter applied to an Excel defined Table. What you will learn in this article. Build a formula that counts unique distinct values in a filtered table using a modified version of Laurent Longre's formula.

Returns the number of distinct, nonempty tuples in a set.

Syntax

Arguments

Set_Expression
A valid Multidimensional Expressions (MDX) expression that returns a set.

Remarks

The DistinctCount function is equivalent to Count(Distinct(Set_Expression), EXCLUDEEMPTY).

Examples

The following query shows how to use the DistinctCount function:

WITH SET MySet AS

{[Customer].[Customer Geography].[Country].&[Australia],[Customer].[Customer Geography].[Country].&[Australia],

[Customer].[Customer Geography].[Country].&[Canada],[Customer].[Customer Geography].[Country].&[France],

[Customer].[Customer Geography].[Country].&[United Kingdom],[Customer].[Customer Geography].[Country].&[United Kingdom]}

*

{([Date].[Calendar].[Date].&[20010701],[Measures].[Internet Sales Amount] )}

//Returns the value 3 because Internet Sales Amount is null

//for the UK on the date specified

MEMBER MEASURES.SETDISTINCTCOUNT AS

DISTINCTCOUNT(MySet)

SELECT {MEASURES.SETDISTINCTCOUNT} ON 0

FROM [Adventure Works]

How To Use Distinct Count In Excel 2010 Pivot Table

The DistinctCount function returns the distinct number of items in a set; in this example, the optional second parameter is used to exclude items that don’t have a value for a given tuple. In this case there are four distinct items in the set in the first parameter, but the function returns three because only Australia, Canada and France have data for July 1st 2001 for Internet Sales Amount.

See Also

Count (Set) (MDX)
MDX Function Reference (MDX)