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
        ADDCOLUMNS (
            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

Top selling 1

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 (
        ADDCOLUMNS (
            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

Top selling 2

Advertisements

“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.