Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Excelent Sir, Thank you so much great knowlege of dax function, Can you give the traning of dax function
@Ravinder1
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
Hi @Ravinder1
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.
Excelent Sir, Thank you so much great knowlege of dax function, Can you give the traning of dax function
Dear Sir,
i am unable to two column top 10 in need %. Plese help. issue reflect rank 2 & 3
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 |
Hello @Ravinder1,
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:
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
19 | |
19 | |
18 | |
18 |
User | Count |
---|---|
37 | |
25 | |
18 | |
17 | |
13 |