Top 1/2/N Products

[shivkonar]

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

[Sean · Accepted solution]

@shivkonar

Give this a try...

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

HTH!

[dbravo · Accepted solution]

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.

[venug20]

Hi,

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

[shivkonar]

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?

[dbravo]

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

[Sean · Accepted solution]

@shivkonar

Give this a try...

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

HTH!

[venug20]

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