Forum Discussion
Lucas_Radebe
1 year agoRegular Visitor
Creating "Others" category for a chart
Hi, I have about 20 Brands ("Brand2" in my data) and am trying to put the top 5 by volume in a chart then the "Others" in a grouping. I have created 3 lines of DAX : Total Volume = (SUM('C2 Da...
- 1 year ago
Hi Lucas_Radebe
if you want to be able to use the rank measure in a legend, you must materialize the result using a table that contains the rank numbers.
Top N Sales with Other = VAR SummarizedSales = SUMMARIZECOLUMNS ( 'Product'[Model], "@rank", [Product Model Rank By Sales], "@sales", [Sales] ) VAR CategorizedSales = ADDCOLUMNS ( SummarizedSales, "@New Model", IF ( [@rank] <= [Parameter Value], [Model], "Other" ) ) VAR FilteredSales = FILTER ( CategorizedSales, [@New Model] IN VALUES ( 'Models with Other'[Model] ) ) RETURN SUMX ( FilteredSales, [@sales] )The TopN number in the above measure is parameterized but can be replaced with an actual number.
Please see the attached pbix.
Ashish_Mathur
Super User
1 year agoHi,
I have solved a similar question in the attahed PBI file.