Using TOCSV to Show Top N

[BrianConnelly]

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