Showing results for 
Search instead for 
Did you mean: 
Resolver III
Resolver III

Using TOCSV to Show Top N

For demonstration, I am using the sample Contoso PBIX (

Here we can see a list of countries by their total sales such as.....


I created a few basic measures for Sales and Rank by Country such as...

Sales = SUM('Sales'[SalesAmount])

Ranking on Country = 
IF (
    ISINSCOPE ( 'Geography'[RegionCountryName] ),
    RANKX (
        ALLSELECTED ( 'Geography'[RegionCountryName] ),

 I am going to use the Rank measure and Sale measure for sorting in my summary table.  I also created a parameter table to allow the user to select the Top 5,10,15,20, and 25 countries.



Now, there are other ways to concatenate the TOP N countries by sales, but this tip is about using the new function called TOCSV.  I wrote the following measure that does the following:

1) Summarizes the country and total sales while formatting the [Sales] measure in the summary table.

2) Keep the Top N based on what option is selected from the parameter table or defaults to Top N = 5.

3) Uses the TOCSV to create a comma seperate list.

Top N Countries = 
VAR summaryTable = SUMMARIZECOLUMNS('Geography'[RegionCountryName],"TotalSales",FORMAT([Sales]," $#,##"))
VAR keepTopN = TOPN(SELECTEDVALUE('Top N'[Show Top],5),summaryTable,[Ranking on Country],ASC,[TotalSales], DESC) 
VAR concateTheRecords = TOCSV(keepTopN,25,",",FALSE())
RETURN concateTheRecords

The results in this example are...




Helpful resources

Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors