Scenario:
Suppose we have a Data Model 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:--------------------------------------------------------------------------------------------------------
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.