Count your top selling products with DAX

One of my customers wanted to count their top selling products. Since sharing is caring I will show you how this can be achived in this post ðŸ™‚

I want to list all products that sold for at least 5000 dollars in the year 2005.

```DEFINE
MEASURE 'Internet Sales'[Sales] =
SUM ( 'Internet Sales'[Sales Amount] )
EVALUATE
FILTER (
SUMMARIZE (
CROSSJOIN (
VALUES ( 'Date'[Calendar Year] ),
VALUES ( 'Product'[Product Id] )
),
'Date'[Calendar Year],
'Product'[Product Id]
),
'Internet Sales'[Sales] >= 5000
&& 'Date'[Calendar Year] = 2005
),
"Sum of Sales", 'Internet Sales'[Sales]
) ORDER BY 'Internet Sales'[Sales] DESC
```

And the result will look like this

If you want to only display the number of products you can add “row” and “countrows” like this

```DEFINE
MEASURE 'Internet Sales'[Sales] =
SUM ( 'Internet Sales'[Sales Amount] )
EVALUATE
ROW (
"Count", COUNTROWS (
FILTER (
SUMMARIZE (
CROSSJOIN (
VALUES ( 'Date'[Calendar Year] ),
VALUES ( 'Product'[Product Id] )
),
'Date'[Calendar Year],
'Product'[Product Id]
),
'Internet Sales'[Sales] >= 5000
&& 'Date'[Calendar Year] = 2005
),
"Sum of Sales", 'Internet Sales'[Sales]
)
)
)
```

And the result will look like this

“Distinctcount” in DAX and MDX

If you want to count distinct values in DAX you have at least two ways of doing this. You can use the distinctcount or you can use countrows with distinct.

A sample can be viewed below

```EVALUATE
SUMMARIZE (
'Product',
"Number of products version 1", DISTINCTCOUNT ( 'Product'[Product Id] ),
"Number of products version 2", COUNTROWS ( DISTINCT ( 'Product'[Product Id] ) )
)```

In MDX the same thing can be done using this code

```WITH MEMBER CountDistinct
AS COUNT(DISTINCT [Product].[Product Id].[Product Id])

SELECT {[Measures].[CountDistinct]} ON 0
FROM [Internet Operation]
```

You also have a distinctcount in MDX that can be used.