topic TOP 5 & rest of other in DAX Commands and Tips

TOP 5 & rest of other

Anonymous — Sun, 17 Sep 2023 07:05:02 GMT

Dear, Please help i am unable to top 5 product and rest of other with dax function

Table
PRODUCT_ID	Amount	Value	Per %	Rank1.1 (this is not column) using by measure
1	101	84.11	83.28%	1
2	85.4	59.83	70.03%	2
3	71.9	55.08	76.57%	3
4	38.2	31.13	81.54%	4
5	4.4	3.65	82.61%	5
6	3.8	3.40	89.07%	Other
7	1.8	1.56	84.49%	Other
8	0.3	0.21	80.52%	Other
9	0	0.02	100.00%	Other
10	0			Other
11	0			Other
12	0	0.00	30.78%	Other
Total	306.8	238.99	77.9%


Required table
PRODUCT_ID	Amount	Value	Per %	Rank1.1 (this is not column) using by measure
1	101	84.11	83.28%	1
2	85.4	59.83	70.03%	2
3	71.9	55.08	76.57%	3
4	38.2	31.13	81.54%	4
5	4.4	3.65	82.61%	5
Other	5.9	5.191	87.98%	Other
Total	306.8	238.99	77.9%

Re: TOP 5 & rest of other

Sahir_Maharaj — Sun, 17 Sep 2023 17:12:47 GMT

Hello Anonymous,

Can you please try these steps:

1. Create a Supporting Calculated Column

Product Category = IF(YourTable[Rank1.1] <= 5, YourTable[PRODUCT_ID], "Other")

2. Create a Table Visualization using the following columns:

Product Category (The calculated column you created above)
Amount
Value
Per %

3. Create Measures

Amount Measure = VAR CurrentCategory = SELECTEDVALUE(YourTable[Product Category]) RETURN IF(CurrentCategory = "Other", SUM(YourTable[Amount]), BLANK())

4. Add a Total Row

Total Amount = SUM(YourTable[Amount])

Should you require further details or information, please do not hesitate to reach out to me.

Re: TOP 5 & rest of other

tamerj1 — Mon, 18 Sep 2023 06:59:52 GMT

Hi Anonymous
The approach could be different depending on the specific case. This is an approach applied to one measure you may use it as a starting point.

Re: TOP 5 & rest of other

Anonymous — Tue, 19 Sep 2023 11:13:20 GMT

Excelent Sir, Thank you so much great knowlege of dax function, Can you give the traning of dax function

Re: TOP 5 & rest of other

Anonymous — Tue, 19 Sep 2023 12:30:44 GMT

Dear Sir,

i want ascending order rank column.

Amount with Other =
VAR ProductSalesTable =
    ADDCOLUMNS (
        ALLSELECTED ( 'Produts With Other'[N_Product_ID] ),
        "@Amount", [Ann Prm]
    )
VAR AllSales = SUMX ( ProductSalesTable, [@Amount] )
VAR Top5SalesTable = TOPN ( 10, ProductSalesTable, [@Amount] )
VAR Top5Sales = SUMX ( Top5SalesTable, [@Amount] )
VAR OtherSales = AllSales - Top5Sales
VAR Top5SalesWithRank = ADDCOLUMNS ( Top5SalesTable, "@Rank", RANKX ( Top5SalesTable, [@Amount] ) & "" )
VAR Top5SalesWithOther = UNION ( Top5SalesWithRank, { ( "Other", OtherSales, "Other" ) } )
VAR ProductsToShow = FILTER ( Top5SalesWithOther, [N_PRODUCT_ID] IN VALUES ( 'Produts With Other'[N_PRODUCT_ID] ) )
VAR RanksTable = VALUES ( Ranks[Rank] )
VAR FinalTable = FILTER ( CROSSJOIN ( ProductsToShow, RanksTable ), [@Rank] = Ranks[Rank] )
VAR Result = SUMX ( FinalTable, [@Amount] )
RETURN
    Result

Re: TOP 5 & rest of other

Anonymous — Tue, 19 Sep 2023 12:53:04 GMT

Dear Sir,

i am unable to two column top 10 in need %. Plese help. issue reflect rank 2 & 3

R_Per =

VAR ProductSalesTable =

ADDCOLUMNS (

ALLSELECTED ( 'Produts With Other'[N_Product_ID] ),

"@Amount1", [Per Val]

)

VAR AllSales = SUMX ( ProductSalesTable, [@Amount1] )

VAR Top5SalesTable = TOPN ( 10, ProductSalesTable, [@Amount1] )

VAR Top5Sales = SUMX ( Top5SalesTable, [@Amount1] )

VAR OtherSales = AllSales - Top5Sales

VAR Top5SalesWithRank = ADDCOLUMNS ( Top5SalesTable, "@Rank", RANKX ( Top5SalesTable, [@Amount1] ) & "" )

VAR Top5SalesWithOther = UNION ( Top5SalesWithRank, { ( "Other", OtherSales, "Other" ) } )

VAR ProductsToShow = FILTER ( Top5SalesWithOther, [N_PRODUCT_ID] IN VALUES ( 'Produts With Other'[N_PRODUCT_ID] ) )

VAR RanksTable = VALUES ( Ranks[Rank] )

VAR FinalTable = FILTER ( CROSSJOIN ( ProductsToShow, RanksTable ), [@Rank] = Ranks[Rank] )

VAR Result = SUMX ( FinalTable, [@Amount1] )

RETURN

Result

Re: TOP 5 & rest of other

tamerj1 — Wed, 20 Sep 2023 06:32:02 GMT

Anonymous
For sorting you can simply add index column (integer) to the ranks table to use it as a "sort by column"

However, note that the ranking can be applied to only one measure, all other measures shall be evaluated following the ranking of that measure. In my example file the ranking follows the [Sales Amount] measure, if I want to add the another measure (for example [Sales Amount LY]) then the {Amount With Other LY] should be evaluated only for the very same top 5 products (based on [Sales Amount] only) as follows

topic TOP 5 &amp; rest of other in DAX Commands and Tips

TOP 5 & rest of other

Re: TOP 5 & rest of other

Re: TOP 5 & rest of other

Re: TOP 5 & rest of other

Re: TOP 5 & rest of other

Re: TOP 5 & rest of other

Re: TOP 5 & rest of other

topic TOP 5 & rest of other in DAX Commands and Tips