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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.