Forum Discussion

Lucas_Radebe's avatar
Lucas_Radebe
Regular Visitor
1 year ago
Solved

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...
  • danextian's avatar
    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.