Saturday, April 8, 2023

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

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog