- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
TOP 5 & rest of other
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% |
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Excelent Sir, Thank you so much great knowlege of dax function, Can you give the traning of dax function
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Excelent Sir, Thank you so much great knowlege of dax function, Can you give the traning of dax function
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dear Sir,
i am unable to two column top 10 in need %. Plese help. issue reflect rank 2 & 3
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂
Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
05-08-2024 01:53 PM | |||
10-01-2024 02:07 PM | |||
01-07-2025 12:48 PM | |||
01-03-2025 08:14 AM | |||
10-07-2024 10:05 AM |
User | Count |
---|---|
14 | |
14 | |
11 | |
11 | |
8 |