Scenario:
Suppose we have a Power BI Data Model as per below.
Requirement:
We need to find the dynamic Top N (say Top 3) Products based on the Total Sales, and the remaining of Total Sales other than Top N, has to show as Other Product Category (Others).By default the Product "Others" will not have any Sales value. Also, when all the Products are selected, then the value of "Others" will be Zero(0).
We can fulfill the above requirement, as discussed in below steps.
To achieve the above requirement, we need to add a "Others" Product in the Products table if it does not exist.
We can add the "Others" as a Product to tbl_Products table using Table.InsertRows function in Power Query.
let
Source = Excel.Workbook(File.Contents(DataSource), null, true),
Data_Sheet = Source{[Item="tbl_Products",Kind="Sheet"]}[Data],
PromoteHeaders = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true]),
RowsCount = Table.RowCount(PromoteHeaders),
InsertRows = Table.InsertRows(PromoteHeaders,
RowsCount,
{[Prod_Id=999999,Prod_Name="Others"]}),
ChangedType = Table.TransformColumnTypes(InsertRows,{{"Prod_Id", Int64.Type}, {"Prod_Name", type text}})
in
ChangedType
We can create a In Memory static table for TOPN Selection, using DATATABLE Function.
3) Create the required DAX Measures for TOPN Calculations:
Here, I am showing each Calculation in a different Measure only for our Understanding.
Total Sales = SUMX ( tbl_Sales, ( tbl_Sales[Unit_Price] * tbl_Sales[Units_Sold] ))
---------------------------------------------------------------------------Total Products Sales =
VAR vTotalProdSales = CALCULATE([Total Sales], ALL(tbl_Products))
RETURN
vTotalProdSales
---------------------------------------------------------------------------
Top N Products Sales =
VAR vNum = IF(HASONEVALUE(FilterTopN[Top N]),
SELECTEDVALUE(FilterTopN[Top N]),MAX(FilterTopN[Top N]))
VAR vTopProd = TOPN(vNum,
ALLSELECTED(tbl_Products),
[Total Sales],
DESC )
VAR vTopProdSales=CALCULATE( [Total Sales],KEEPFILTERS(vTopProd))
RETURN vTopProdSales
---------------------------------------------------------------------------
Top N Products Total Sales =
VAR vNum = IF(HASONEVALUE(FilterTopN[Top N]),
SELECTEDVALUE(FilterTopN[Top N]),MAX(FilterTopN[Top N]))
VAR vTopProd = TOPN(vNum,
ALLSELECTED(tbl_Products),
[Total Sales],
DESC )
VAR vTopProdTotalSales =CALCULATE( [Total Sales],vTopProd)
RETURN vTopProdTotalSales
---------------------------------------------------------------------------
% of Top N Product Sales =
DIVIDE([Top N Products Total Sales], [Total Products Sales])
---------------------------------------------------------------------------
Top N Selling Products =
VAR vNum = IF(HASONEVALUE(FilterTopN[Top N]),
SELECTEDVALUE(FilterTopN[Top N]),MAX(FilterTopN[Top N]))
VAR vTopProd= TOPN(vNum,
ALLSELECTED(tbl_Products[Prod_Name]),
[Total Sales],
DESC )
RETURN
CONCATENATEX(vTopProd,[Prod_Name],"|",[Total Sales], DESC )
---------------------------------------------------------------------------
Other Products Sales =
VAR vNum = 3
VAR vTotalProdSales = CALCULATE([Total Sales], ALL(tbl_Products))
VAR vTopProd = TOPN(3,
ALLSELECTED(tbl_Products),
[Total Sales],
DESC )
VAR vTopProdTotalSales =CALCULATE( [Total Sales],vTopProd)
VAR vOtherSales = (vTotalProdSales-vTopProdTotalSales)
RETURN vOtherSales
4) Add the Month and TopN Slicers and Visualize the Data
Now we can see the result of the above Measures for Top 3 Products per Month in a Table Visual as shown below.
Notes:
We can combine the logic of all the above Measures into one Measure and can see the result as per below:TopN_OtherProducts =
VAR vNum = IF(HASONEVALUE(FilterTopN[Top N]),
SELECTEDVALUE(FilterTopN[Top N]),MAX(FilterTopN[Top N]))
VAR vSelectProd = SELECTEDVALUE(tbl_Products[Prod_Name])
VAR vTotalProdSales = CALCULATE([Total Sales], ALL(tbl_Products))
VAR vTopProd = TOPN(vNum,
ALLSELECTED(tbl_Products),
[Total Sales],
DESC )
VAR vTopProdSales=CALCULATE( [Total Sales],KEEPFILTERS(vTopProd))
VAR vTopProdTotalSales =CALCULATE( [Total Sales],vTopProd)
VAR vOtherSales = (vTotalProdSales-vTopProdTotalSales)
VAR vResult = IF(vSelectProd="Others", vOtherSales,vTopProdSales)
RETURN
vResult
Output:
Notes:
We can create the dynamic custom Title and a Note using the below expressions:
Title1 = IF(HASONEVALUE(FilterTopN[Top N]),
"Top " & SELECTEDVALUE(FilterTopN[Top N]) &" Products & Others Sales",
"Top " & MAX(FilterTopN[Top N]) &" Products & Others Sales"
)
Note1 = IF(HASONEVALUE(FilterTopN[Top N]),
"( Top " & SELECTEDVALUE(FilterTopN[Top N]) & " Products Sales contributes to : "
& FORMAT([% of Top N Product Sales],"#.##%") & " of Total Sales: "&
[Total Products Sales] & " )",
"( Top " & MAX(FilterTopN[Top N]) & " Products Sales contributes to : "
& [% of Top N Product Sales] & " of Total Sales: "& [Total Products Sales] & " )"
)
------------------------------------------ End of Article -----------------------------------------
Additional Helper Notes:
To include an (All) option in the Top N slicer, define its corresponding TopN_Value as BLANK() in the Top N selector table.
ref_TopN_Selector =
VAR tbl_Variables=
DATATABLE(
"TopN_ID",INTEGER, "TopN_Name", STRING,
"TopN_Value", INTEGER,"TopN_Code", STRING,
{
{1,"(All)",BLANK(),"ALL"},{2,"Top 5",5,"Top_5"},
{3,"Top 10",10,"Top_10"},{4,"Top 15",15,"Top_15"}
}
)
RETURN
When (All) is selected in the Top N slicer, the [TopN Value] measure returns BLANK().
This blank value is handled in the extended Top N logic by using the distinct product count to return All Products when [TopN Value] is NULL/Blank for (All).
When a specific Top N value is selected, it retains the existing behavior of returning sales for the Top N Products.
Sales for TopN Products =
VAR _Selected_Product = SELECTEDVALUE ( tbl_Products[Prod_ID] )
VAR _Distinct_Products =
CALCULATE(COUNTROWS(VALUES(tbl_Products[Prod_ID])), ALL(tbl_Products))
VAR _Adjusted_TopN = COALESCE([TopN Value],_Distinct_Products)
VAR _Filtered_Products =
FILTER (
ALLSELECTED ( tbl_Products ),
--NOT ISBLANK ( tbl_Products[Prod_ID] )
NOT ISBLANK ( [Total Sales] )
)
VAR _TopN_Products =
TOPN (
_Adjusted_TopN,
_Filtered_Products,
[Total Sales], DESC,
tbl_Products[Prod_ID], ASC
)
RETURN
CALCULATE (
[Total Sales],
KEEPFILTERS ( _TopN_Products )
)
The following flag measure returns 1 when the selected Product is within the Top N Products based on Total Sales; otherwise, it returns 0.
TopN Products by Sales Flag =
VAR _Selected_Product = SELECTEDVALUE ( tbl_Products[Prod_ID] )
VAR _Distinct_Products =
CALCULATE (
COUNTROWS ( VALUES ( tbl_Products[Prod_ID] ) ),
ALL ( tbl_Products )
)
VAR _Adjusted_TopN = COALESCE ([TopN Value], _Distinct_Products )
VAR _Filtered_Products =
FILTER (
ALLSELECTED ( tbl_Products ),
--NOT ISBLANK ( tbl_Products[Prod_ID] )
NOT ISBLANK ( [Total Sales] )
)
VAR _TopN_Products =
TOPN (
_Adjusted_TopN,
_Filtered_Products,
[Total Sales], DESC,
tbl_Products[Prod_ID], ASC
)
RETURN
INT (
_Selected_Product
IN SELECTCOLUMNS (
_TopN_Products,
"Product_ID", tbl_Products[Prod_ID]
)
)
Related Articles:
https://excelkingdom.blogspot.com/2023/04/how-to-display-top-selling-products-per.html
https://excelkingdom.blogspot.com/2020/11/how-to-create-top-n-sales-summary-in.html
https://excelkingdom.blogspot.com/2018/11/how-to-dynamically-filter-top-n-based.html
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. If you wish, please share your genuine Feedback or comments only related to this Blog Posts. It is my humble request that, please do not post any Spam comments or Advertising kind of comments, which will be Ignored.