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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BrianConnelly
Resolver III
Resolver III

Using TOCSV to Show Top N

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

BrianConnelly_0-1679586581236.png

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.

BrianConnelly_1-1679586733715.png

 

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

BrianConnelly_2-1679587170693.pngBrianConnelly_3-1679587230186.png

 

0 REPLIES 0

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors