Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Solved! Go to 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.
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.
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.