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:

----------------------------------------------------------------------

If we want to see Top 5 Products with other Products details, we can use the following logic:

Top 5 Product Sales =

VAR vTopProdSales = TOPN(5,

      CROSSJOIN(ALLSELECTED(tbl_Calendar), ALLSELECTED(tbl_Products[Prod_Name])),

      [Total Sales],

      DESC )

RETURN

CALCULATE( [Total Sales],KEEPFILTERS(vTopProdSales))

Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------