Best Selling Product per market

[gbarr12345]

Hi,

I'm trying to create a measure that shows the best selling product per market.

However, when I inserted my measure it's giving each market the same product.

Any idea where I'm going wrong?

Code below and PBIX attached.

Best Selling product per market = FIRSTNONBLANK( TOPN( 1, VALUES( 'Dimension_Item Table'[Item Description] ),

    SUM( 'Sales Table'[Amount]) ) , 1)

Many Thanks in advance.

https://drive.google.com/file/d/1ZDfNmrZ60eMfjB1WU3cSMcOGrQqkPt7X/view?usp=drive_link

[PawelWrona · Accepted solution]

What in case you have multiple items with same sales per market? You could use following formula that will concatenate such items:

Best Selling product per market v2 = 
VAR summarizedTable =                                           // Creates virtual table of sales per item
    ADDCOLUMNS(
		DISTINCT('Dimension_Item Table'[Item Description]),     // DISTINCT gets rid of blank rows
		"@Sales", CALCULATE(SUM('Sales Table'[Amount]))
	)

VAR maxSales =                                                  // Gets max sales
    MAXX(summarizedTable, [@Sales])

VAR filteredTable =                                             // Returns all the rows with max sales
    FILTER(summarizedTable, [@Sales] = maxSales)

VAR result =                                                    // Concatenate in case there are multiple items with same sales
    CONCATENATEX(
        filteredTable,
        'Dimension_Item Table'[Item Description],
        ","
    )

RETURN {result}

I think it should work as per your requirements:

I included comments but let me know if you have questions. TOPN can be resource expensive with large data. This formula is quite fast.

[PawelWrona · Accepted solution]

I am not sure I follow you Chain related question. When I use the chain from your model it works fine:

As for the question to bring the top sales as well, you could use the same code but till "maxSales" variable, like here:

Top Sales = 
VAR summarizedTable =                                           // Creates virtual table of sales per item
    ADDCOLUMNS(
		DISTINCT('Dimension_Item Table'[Item Description]),     // DISTINCT gets rid of blank rows
		"@Sales", CALCULATE(SUM('Sales Table'[Amount]))
	)

VAR maxSales =                                                  // Gets max sales
    MAXX(summarizedTable, [@Sales])


RETURN {maxSales}

And here is the result:

[PawelWrona · Accepted solution]

What in case you have multiple items with same sales per market? You could use following formula that will concatenate such items:

Best Selling product per market v2 = 
VAR summarizedTable =                                           // Creates virtual table of sales per item
    ADDCOLUMNS(
		DISTINCT('Dimension_Item Table'[Item Description]),     // DISTINCT gets rid of blank rows
		"@Sales", CALCULATE(SUM('Sales Table'[Amount]))
	)

VAR maxSales =                                                  // Gets max sales
    MAXX(summarizedTable, [@Sales])

VAR filteredTable =                                             // Returns all the rows with max sales
    FILTER(summarizedTable, [@Sales] = maxSales)

VAR result =                                                    // Concatenate in case there are multiple items with same sales
    CONCATENATEX(
        filteredTable,
        'Dimension_Item Table'[Item Description],
        ","
    )

RETURN {result}

I think it should work as per your requirements:

I included comments but let me know if you have questions. TOPN can be resource expensive with large data. This formula is quite fast.

[gbarr12345]

That worked thank you!

One more quick question. Is there a way to be able to drag in the correct amount instead of the overall amount as per my screenshot?

[gbarr12345]

Also i nmy data I'm trying to get the best selling item per chain. Some of the chains have no items sold so as a result it's throwing out all the products instead of being blank. Is there a way to fix this also?

[PawelWrona · Accepted solution]

I am not sure I follow you Chain related question. When I use the chain from your model it works fine:

As for the question to bring the top sales as well, you could use the same code but till "maxSales" variable, like here:

Top Sales = 
VAR summarizedTable =                                           // Creates virtual table of sales per item
    ADDCOLUMNS(
		DISTINCT('Dimension_Item Table'[Item Description]),     // DISTINCT gets rid of blank rows
		"@Sales", CALCULATE(SUM('Sales Table'[Amount]))
	)

VAR maxSales =                                                  // Gets max sales
    MAXX(summarizedTable, [@Sales])


RETURN {maxSales}

And here is the result:

[gbarr12345]

Thank you so much for your help, it's much appreciated!

[amustafa]

Here's the updated code...

CALCULATE(
    FIRSTNONBLANK('Dimension_Item Table'[Item Description], SUM('Sales Table'[Amount])),
    FILTER(
        ALLNOBLANKROW('Dimension_Item Table'),
        RANKX(
            ALLNOBLANKROW('Dimension_Item Table'),
            CALCULATE(SUM('Sales Table'[Amount])),
            ,
            DESC,
            Dense
        ) = 1
    )
)

[gbarr12345]

Hi Mustafa,

Thank you so much for the code. That worked. 

Is there a way to be able to drag in the correct amount for the best selling product also?

[amustafa · Accepted solution]

Sure, here the DAX...

TopProductAmountByMarket = 
CALCULATE(
    SUM('Sales Table'[Amount]), 
    FILTER(
        'Dimension_Item Table',
        'Dimension_Item Table'[Item Description] = [TopProductByMarket]
    )
)

[gbarr12345]

Thank you so much, that worked!

Thank you!

[amustafa]

Try the updated DAX...

Best Selling Product per Market NEW = 
VAR TopProductByMarket = 
    TOPN(
        1, 
        VALUES('Dimension_Market Table'[Market]), 
        CALCULATE(
            SUM('Sales Table'[Amount]), 
            ALL('Dimension_Item Table')
        ), 
        DESC
    )
RETURN
    FIRSTNONBLANK(
        'Dimension_Item Table'[Item Description], 
        CALCULATE(
            SUM('Sales Table'[Amount]), 
            TopProductByMarket
        )
    )

Results.

[gbarr12345]

I tried your code and still seem to be getting the incorrect best selling product.