cancel
Showing results for
Did you mean:
Regular Visitor

## Top 1/2/N Products

Hi ,

I am just making up a scenario to practice DAX. On a Power BI report, I want to show 2 Card Visuals like below:

DataModel - 1 to Many relationship between Product and Sales table. [Total Sales] = SUM(Sales[Quantity]) * SUM(Sales[Unit Price])

How do I calculate the highest selling product, 2nd highest selling product or in general nth highest selling product?

Thanks,

Shiv

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

## Re: Top 1/2/N Products

@shivkonar

Give this a try...

```Top 2nd Product =
FIRSTNONBLANK ( TOPN ( 2, VALUES ( Products[Product] ), [Total Sales] ), 1 )```

HTH!

Frequent Visitor

## Re: Top 1/2/N Products

Hi,

You have to use the TOPN function, it has 3 arguments, first the number of elements to return (the "N" for TOPN"), a table to extract the top values and an order by expression, actually TOPN has more than 3 arguments but from the third they are only for ordering purposes.

I'd build a measure like this:

```Top 1 =
TOPN (
1,
SELECTCOLUMNS ( -- Here we make a temporary summary table
'thesourcetable',
"Item", 'thesourcetable'[item] -- this is the item that we want to know which is in the top
),
[MyMeasure], -- A measure that we use to order by
DESC -- DESC for getting the top if you want the "Top of the bottom", use ASC
)```

```Top 1 =
TOPN (
1,
SELECTCOLUMNS (
product,
"product_name", product[product_name]
),
[Total Sales],
DESC
)```

I hope this helps, bye.

6 REPLIES 6
Super User

## Re: Top 1/2/N Products

@shivkonar

Give this a try...

```Top 2nd Product =
FIRSTNONBLANK ( TOPN ( 2, VALUES ( Products[Product] ), [Total Sales] ), 1 )```

HTH!

Member

## Re: Top 1/2/N Products

@shivkonar

Just go through below like for your TOP N solution...

https://www.dropbox.com/s/qh6p5or4nyll20z/Sample-TopN-Sales.pbix?dl=0

1.  First you should get RANK for you sales like below....

Rank = RANKX(ALL(Orders[Customer Name]), [Totalsales])

2.  Create Sample TOPN table for create Top N Sales.... Like below

TopNSelect = IF(HASONEFILTER(TopNTable[TopN]), VALUES(TopNTable[TopNValue]), MAXX(VALUES(Orders[Customer Name]), [Totalsales]))

3. To show Top N Sales using below DAX...

Top Sales = IF([Rank]<=[TopNSelect], [Totalsales])

If it is meet your requirement, Pls accept as solution...

Frequent Visitor

## Re: Top 1/2/N Products

Hi,

You have to use the TOPN function, it has 3 arguments, first the number of elements to return (the "N" for TOPN"), a table to extract the top values and an order by expression, actually TOPN has more than 3 arguments but from the third they are only for ordering purposes.

I'd build a measure like this:

```Top 1 =
TOPN (
1,
SELECTCOLUMNS ( -- Here we make a temporary summary table
'thesourcetable',
"Item", 'thesourcetable'[item] -- this is the item that we want to know which is in the top
),
[MyMeasure], -- A measure that we use to order by
DESC -- DESC for getting the top if you want the "Top of the bottom", use ASC
)```

```Top 1 =
TOPN (
1,
SELECTCOLUMNS (
product,
"product_name", product[product_name]
),
[Total Sales],
DESC
)```

I hope this helps, bye.

Member

## Re: Top 1/2/N Products

Hi,

I appreciate your theoretical knowledge. @shivkonar he wants solution for his query. In my point view, he can search TOPN syntax….

Regular Visitor

## Re: Top 1/2/N Products

Thank you both @venug20 and @dbravo

Both the solutions were quite insightful. I can't accept both posts as solutions, can I?

In either case, I will have to do 2 calculations, 1 for the actual sales numbers and 1 for the name of the product and place these on 2 cards, correct?

Frequent Visitor

## Re: Top 1/2/N Products

Hi,

Yes, you have to make 2 calculations but you can also use a "TOPN" filter then you will have one calculation only (the order by measure). This is a quick solution, but I did'n recommend it because you can't access to this top, it only "lives" on the visual filter.

Bye