Best selling Product Description


#1

Hi,

I’m trying to show a table of data showing a sales persons total sales for the period, and show the best selling product description.

So

Sales Person Qty Sales Amount Best Product
Jim Smith 3200 42000 Widget A
Mary Jane 6000 45000 Widget K

How do i get the description of their best selling product?

Thanks!


#2

Check out how this one is calculated and then you’ll see how to use this idea on your scenario

Let me know how you go with this

Sam


#3

Hi Sam,

Thanks very much.

Your example works perfectly, however i am having to use MSSQL Tabular for this solution (2016 RTM), I upgraded the Service Pack to SP2 and it was still failing with the message “MAXX takes an arguement that evaluates to numbers or dates and cannot work with values of type string”. I tried on an instance of SQL 2017 Tabular and it works! So now i just need to deal with that!

Thanks again.

Top Sold Product = 
var t = 
MAXX(
    TOPN( 1,
    SUMMARIZE(
        Product,
        Product[ProdDescr],
        "MostSold", [Sales Amount]),
    [MostSold] ),
Product[ProdDescr])

RETURN
    IF(ISBLANK([Sales Amount]), BLANK(), t)

#4

Right, that’s annoying.

I am honestly not 100% sure why it wouldn’t work on the earlier version. Maybe it’s something to do with MAXX not being available.

I’m not 100% on that currently.