cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shivkonar Regular Visitor
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:

 

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
Sean Super Contributor
Super Contributor

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

dbravo Frequent Visitor
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
Sean Super Contributor
Super Contributor

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

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

dbravo Frequent Visitor
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

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

 

shivkonar Regular Visitor
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?

Highlighted
dbravo Frequent Visitor
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
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 153 members 1,704 guests
Please welcome our newest community members: