Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Ravinder1
Frequent Visitor

TOP 5 & rest of other

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

 

Table    
PRODUCT_IDAmountValuePer %Rank1.1 (this is not column) using by measure
110184.1183.28%1
285.459.8370.03%2
371.955.0876.57%3
438.231.1381.54%4
54.43.6582.61%5
63.83.4089.07%Other
71.81.5684.49%Other
80.30.2180.52%Other
900.02100.00%Other
100  Other
110  Other
1200.0030.78%Other
Total306.8238.9977.9% 
     
     
Required table    
PRODUCT_IDAmountValuePer %Rank1.1 (this is not column) using by measure
110184.1183.28%1
285.459.8370.03%2
371.955.0876.57%3
438.231.1381.54%4
54.43.6582.61%5
Other5.95.19187.98%Other
Total306.8238.9977.9% 
1 ACCEPTED SOLUTION

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

 

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

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

1.png2.png

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

3.png

tamerj1
Super User
Super User

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.

2.png3.png4.png1.png

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

 

Ravinder1_2-1695127851385.png

 

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

 

Dear Sir,

i want ascending order rank column.

Ravinder1_1-1695126504827.png

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

 

 

Sahir_Maharaj
Super User
Super User

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:

  • 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?
➤ Lets connect on LinkedIn: Join my network of 13K+ 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

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors