Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
zhivaninz
Helper I
Helper I

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

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

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

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!