topic Re: Problem with DAX : Dynamic TopN + Others in Power BI in DAX Commands and Tips

Problem with DAX : Dynamic TopN + Others in Power BI

GhostTIT — Sun, 21 Jul 2024 19:48:12 GMT

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

Re: Problem with DAX : Dynamic TopN + Others in Power BI

GhostTIT — Sun, 21 Jul 2024 19:53:51 GMT

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

Re: Problem with DAX : Dynamic TopN + Others in Power BI

Anonymous — Tue, 23 Jul 2024 01:23:02 GMT

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.

Re: Problem with DAX : Dynamic TopN + Others in Power BI

GhostTIT — Mon, 22 Jul 2024 10:08:44 GMT

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