Using TOCSV to Show Top N

BrianConnelly — Thu, 23 Mar 2023 16:00:38 GMT

For demonstration, I am using the sample Contoso PBIX (https://www.microsoft.com/en-us/download/details.aspx?id=46801).

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] ), [Sales] ) )

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

topic Using TOCSV to Show Top N in DAX Commands and Tips

Using TOCSV to Show Top N