cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent 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:

 

Screen Shot 2018-09-05 at 11.14.33.png

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
Highlighted
Community Champion
Community Champion

Re: Top 1/2/N Products

@shivkonar

Give this a try...

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

HTH! Smiley Happy

View solution in original post

Highlighted
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
)

 

In your case:

 

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

 

For further information please read: https://msdn.microsoft.com/en-us/query-bi/dax/topn-function-dax

I hope this helps, bye.

 

View solution in original post

6 REPLIES 6
Highlighted
Community Champion
Community Champion

Re: Top 1/2/N Products

@shivkonar

Give this a try...

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

HTH! Smiley Happy

View solution in original post

Highlighted
Resolver I
Resolver I

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

Highlighted
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
)

 

In your case:

 

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

 

For further information please read: https://msdn.microsoft.com/en-us/query-bi/dax/topn-function-dax

I hope this helps, bye.

 

View solution in original post

Highlighted
Resolver I
Resolver I

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

 

Highlighted
Frequent 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?

Highlighted
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

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors