Get the name of the top n selling product returned (text value)

[zhivaninz]

The use case is for automated commentary of this form:

Top N product sold $$$ this month a change of X% since last month. Top N+1 product sold $$ this month a change of X5 since last month.

What we want are measures to return the product name for the top ranked product, next top selling product, and so on. We have tried a dozen variations using TOPN/RANKX and while some returned a product name, often they were faulty (due to TOPN not ranking).

Currently we've got code that returns a single row table for the correct RANKX e.g.

Product Rank

Lollipops 2

(this is for the measure returning the name of the second best selling product).

However, how we then turn that into a value for the measure, I'm not sure. LASTNONBLANK won't seem to work as we have created a virtual table and it won't take the declared column names from that table.

So, the use case: 

Create a measure that returns the product name for the top selling product

Create a measure that returns the product name for the next best selling product

[Ashish_Mathur]

Hi,

This measure pattern should work for getting the top selling products (multiple products which have a rank of 1)

Measure = CONCATENATEX(TOPN(1,VALUES(Products[Product]),[Total Sales]),Products[Product],", ")

If it does not work, then share the download link of the PBI file and show the expected result there.

[zhivaninz · Accepted solution]

Hi Ashish,

Your code didn't work, but we developed one that did in the end:

VAR _toprankedtable =
ADDCOLUMNS(
	VALUES(
		DATA_SALES[Product]),
		"Product", DATA_SALES[Product],
		"Sales", [SalesMeasure],
		"Ranking", RANKX(
			VALUES(
				DATA_SALES[Product]),
				[SalesMeasure]
		)
)

VAR _toprankedproduct =
CALCULATE(
	MAXX(
		FILTER(
			_toprankedtable,
			[Ranking]=3
		),
		[Product]
	)
)

return
_toprankedproduct

We just change the rankx value either via slicer or create manual versions to generate the different commentary lines.