Forum Discussion
Problem with DAX : Dynamic TopN + Others in Power BI
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.
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.
- ...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 thisProductTop3andOthersM =
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
