How to display Top Selling Products per Month using CONCATENATEX Function in Power BI

How to display Top N Products by Sales per Month using CONCATENATEX Function in Power BI

Scenario:

Suppose we have a Data Model as per below.

We have a Sales Summary by Month and Product as Shown below:

Now we can find the Top Selling Product per Month and its Sales, by using the TOPN and CONCATENATEX Functions as per below:

Total Sales = SUMX ( tbl_Sales, ( tbl_Sales[Unit_Price] * tbl_Sales[Units_Sold] ))

———————————————————————-

Top Selling Product =

VAR vTopProd= TOPN(1,

SELECTCOLUMNS(tbl_Products,“ProdName”, tbl_Products[Prod_Name]),

[Total Sales],

DESC )

RETURN

CONCATENATEX(vTopProd,[ProdName])

———————————————————————-

Top Product Sales =

VAR vTopProdSales = TOPN(1,

SELECTCOLUMNS(tbl_Products,“ProdName”, tbl_Products[Prod_Name]),

[Total Sales],

DESC )

RETURN

SUMX(vTopProdSales, [Total Sales])

———————————————————————-

% Top Product Sales =

VAR vSales = [Total Sales]

VAR vTopProd = TOPN(1,

tbl_Products,

[Total Sales],

DESC )

VAR vTopProdSales = SUMX(vTopProd, [Total Sales])

RETURN

DIVIDE(vTopProdSales, vSales)

———————————————————————-

TopSellingProdDetails =

VAR vSales = [Total Sales]
VAR vTopProd=  TOPN(1,
    FILTER(tbl_Products, [Total Sales]<> BLANK()),
    [Total Sales],
    DESC )
RETURN
CONCATENATEX(vTopProd,  
    tbl_Products[Prod_Name] & FORMAT([Total Sales], ” | #”) &
    FORMAT(DIVIDE([Total Sales],vSales),” | #.##%”)
    )

———————————————————————-

Result:

Note:

The % Top Product Sales indicates the % of Top Product Sales amount out of Total Sales in that Month and Year for that Product.
———————————————————————-
If we want to see what are the Top 3 Products, we can use the following logic:

Top 3 Selling Products = 
  VAR vTopProd= TOPN(3,
    FILTER(SELECTCOLUMNS(tbl_Products,“ProdName”, tbl_Products[Prod_Name]),
        [Total Sales]<>BLANK()
        ),
    [Total Sales],
    DESC )
RETURN
CONCATENATEX(vTopProd, [ProdName],“|”,[Total Sales],Desc)

Notes:

The use of the SELECTCOLUMNS(tbl_Products,”ProdName”, tbl_Products[Prod_Name]) in the Top N is optional. It is good to use only when the Products table is big and we don’t want to select the entire table. Otherwise we use reference the table tbl_Products.

Result: