Problem with DAX : Dynamic TopN + Others in Power BI

[GhostTIT]

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 · Accepted solution]

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]

...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]

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")

files > https://files.fm/u/cmrns7jct4