In this blog, I’ll show how can you display the Sales of Top “N” products and categorize the rest as “Others”. This is a common need in business reporting, let’s see how can you do this using some DAX & Data Modeling and some visualization tricks.   Display Sales for TOPN & Others – Video   Firstly, let’s go ahead and see what our final output looks like. Before we proceed, let’s take a look at the Data Model.   This post is going to be a long one, so let me summarize the building blocks of this visual. We need to display Product Names along with “Others” on the X-Axis. We need a Calculation / Measure to group the Sales for Top Products and Others. A Slicer to select the number of Top Products displayed in the Visual. Conditional Formatting to color the Top Products and Others. Sorting of the Visual. Dynamic Title that displays the % contribution of Top Products.   #1 Display Product Names and “Others” as a category on the X-Axis A general rule – Anything that is displayed on the x-axis of a chart needs to be physically present as a column of a table. In our scenario we need Product Name and “Others” on the horizontal axis of the chart, that can only be done if we have a column that has product names and “Others”. To do this let’s create a Pseudo Prod Table: Pseudo Prod Table = UNION (    DISTINCT ( Products[Product] ), — to find unique names of the products    DATATABLE (                        — one row table having value “Others”          “Product”, STRING,         { { “Others” } }     )  ) If you create a Matrix Visual using the Products column of the pseudo table you’ll see all products and “Others”.   Let’s also create a one to many relationship between pseudo products table and products table.   Now that we have displayed Product names along with “Others”, let’s create a calculation that can group the sales of Top Products and the rest as “Others”.   #2 Calculation / Measure to group the Sales for Top Products and Others Let’s start with this simple measure Top N Sum Sales = CALCULATE (     [Total Sales],    TOPN ( — top 3 products by Sales          ‘Pseudo Prod Table’,         [Total Sales]     ) ) With the above measure I’ll be able to see the sales of the TOP 3 Products. For instance refer the card visual  Although this measure won’t work if you drag this to the Matrix visual.  Problem – At the product level I see the sales of all the products and not just the top 3 products. Simple reason, when the sales of each product being compared to itself, it would would obviously rank in the Top 3 products and hence all products are displayed. To solve this, I can modify my code as follows, so that the only sales of the top 3 products are displayed against their name. Top N Sum Sales = CALCULATE (     [Total Sales],     KEEPFILTERS (     — will apply the product filter to show only 3 products         TOPN (             3,             ALLSELECTED ( ‘Pseudo Prod Table’ ), — considers all visible products             [Total Sales]         )     ) ) The results look better! Next if I subtract Sales of top 3 products from Total Sales of all the products we get value to be displayed in the “Others” category. Let’s further revise the measure. Top N Sum Sales = VAR TopProdTable =     TOPN (         3,         ALLSELECTED ( ‘Pseudo Prod Table’ ),         [Total Sales]     ) VAR TopProdSales =     CALCULATE (         [Total Sales],         KEEPFILTERS ( TopProdTable )     ) VAR OtherSales =     CALCULATE (         [Total Sales],         ALLSELECTED ( ‘Pseudo Prod Table’ )     )         – CALCULATE ( — subtracting the sales of Top 3 products             [Total Sales],             TopProdTable         ) VAR CurrentProd =     SELECTEDVALUE ( ‘Pseudo Prod Table'[Product] ) RETURN     IF ( — Categorizing as Others and Top Products         CurrentProd <> “Others”,         TopProdSales,         OtherSales     ) Now the Pivot shows the results of TOP 3 Products and Others The above matrix visual when converted to a clustered column chart visual, it looks like this As of now the number 3 to select top 3 products is hardcoded in the measure, to make it dynamic we need a slicer and link the slicer selection to our measure.   #3 Slicer to Select the Top Products Let’s create a New Table Top N Selection TopN Selection = {1,2,3,4,5} Now create a slicer on the value column of this table. To capture the value selected in the slicer, I will finally revise my measure like this. Top N Sum Sales = VAR TopNSelected = — capturing top value selected in the slicer     SELECTEDVALUE ( ‘TopN Selection'[Value] ) VAR TopProdTable =     TOPN (         TopNSelected,         ALLSELECTED ( ‘Pseudo Prod Table’ ),         [Total Sales]     ) VAR TopProdSales =     CALCULATE (         [Total Sales],         KEEPFILTERS ( TopProdTable )     ) VAR OtherSales =     CALCULATE (         [Total Sales],         ALLSELECTED ( ‘Pseudo Prod Table’ )     )         – CALCULATE (             [Total Sales],             TopProdTable         ) VAR CurrentProd =     SELECTEDVALUE ( ‘Pseudo Prod Table'[Product] ) RETURN     IF (         CurrentProd <> “Others”,         TopProdSales,         OtherSales     ) Here’s how it would look like   #4 Conditional Formatting – For Top N and Others I also want to color Top N products as orange and Others as grey. To do this we create a simple measure. Color = SWITCH (    SELECTEDVALUE ( ‘Pseudo Prod Table'[Product] ), — current product    “Others”, “#D9D9D9”, — if “Others”, then grey (hexcode)    “#ED7D31″ — else Orange (hexcode) ) To apply this measure, go to >> format pane >> data color >> use the “fx” to select conditional formatting >> finally use format by field option on this measure. Let’s solve the next problem and sort the bars, Top Products should appear first and “Others” in the end.   #5 Ranking and Sorting the Top Products in order of Sales Consider this measure  Rank = IF (     [Top N SUM]         <> BLANK (),     RANKX (         TOPN (                           SELECTEDVALUE ( ‘TopN Selection'[Value] ),             ALLSELECTED ( … Continue reading Display Top N items and Others in Power BI