Top 1/2/N Products

shivkonar

Give this a try...

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

HTH! :smileyhappy:

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.