Forum Discussion

GhostTIT's avatar
GhostTIT
New Member
2 years ago
Solved

Problem with DAX : Dynamic TopN + Others in Power BI

 

DAX Fridays! #132: Dynamic TopN + Others in Power BI

 

i try to group New column in top3 with A , B , C and Other 

but i cant i dont understand why, can anyone plese send me super sample files .. T_T thank you

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi GhostTIT ,

    Your DAX is available, but it's for use inside a measure not column:



    The article describes the difference between column and measure, you could refer to: The differences between new measure and new column in Power BI - The Data School Down Under

    New columns are static and calculated based on existing data, while measures are dynamic and based on aggregated data.

     

    What's more, you could create the new column or measure under this tab:


    And in case you want to create a column to achieve the output, please follow the method below:

     

     

     

    ProductTop3andOthers_column = 
VAR _a = financials[Sale_column]
VAR _b = RANKX(financials, [Sale_column], , DESC, Dense)
RETURN
IF(_b <= 3 , financials[Product] , "Other")

     

     

     

    Best Regards,

    Xianda Tang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi GhostTIT ,

    Your DAX is available, but it's for use inside a measure not column:



    The article describes the difference between column and measure, you could refer to: The differences between new measure and new column in Power BI - The Data School Down Under

    New columns are static and calculated based on existing data, while measures are dynamic and based on aggregated data.

     

    What's more, you could create the new column or measure under this tab:


    And in case you want to create a column to achieve the output, please follow the method below:

     

     

     

    ProductTop3andOthers_column = 
VAR _a = financials[Sale_column]
VAR _b = RANKX(financials, [Sale_column], , DESC, Dense)
RETURN
IF(_b <= 3 , financials[Product] , "Other")

     

     

     

    Best Regards,

    Xianda Tang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • GhostTIT's avatar
      GhostTIT
      New Member
      2 years ago

       

      ...Thank you for your code I have to create New table to conclude group by use "New table" with SUMMARIZECOLUMNS and then your code .. it' work.. Meanwhile, If I use measure for this
       
      ProductTop3andOthersM =
      var  rank3salesbyProuct = RANKX(ALL(financials[Product]),[sales],,DESC)
      return
      if (rank3salesbyProuct<=3,FIRSTNONBLANK(financials[Product],1),"Others")

      So I cant' use measure for create normal visualize such as "Cluster column chart" and Put this measure to X-axis ?  or any measure only put in value field? thank you
       
       
       
       
       
       
       
       
       
       
       
       
       
       
  • GhostTIT's avatar
    GhostTIT
    New Member
    2 years ago

    this is my sample files 
    Again , i want to create New colum with this code

    ProductTop3andOthers =
    var  rank3salesbyProuct = RANKX(ALL(financials[Product]),[sales],,DESC)
    return
    if (rank3salesbyProuct<=3,financials[Product],"Others")