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 Data'[Volume]))
Brand Rank = RANKX(ALL('C2 Data'[Brand2]), [Total Volume],,DESC,Skip)
Top N Products =IF([Brand Rank] <= 5,SELECTEDVALUE('C2 Data'[Brand2]), "Others")
 
However, I can't use "Top N Products" in the bar chart ; when I try to drag it onto the y-axis, I get the message "This field can't be used here because a non-measure field is required". 
 
I would be really grateful if somebody ould please advise a)why this doesn't work and b) if there is a simple solution ?
 
Many Thanks
Lucas
Need Help
  • danextian's avatar
    danextian
    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.

4 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    1 year ago

    Lucas_Radebe 

    you can try this, if this does not work, pls provide some sample data and expected output.

     

    Brand Grouping =
    VAR TopBrands =
    TOPN(
    5,
    SUMMARIZE('C2 Data', 'C2 Data'[Brand2], "TotalVol", SUM('C2 Data'[Volume])),
    [TotalVol]
    )
    RETURN
    IF(
    'C2 Data'[Brand2] IN TopBrands,
    'C2 Data'[Brand2],
    "Others"
    )

  • Shahid12523's avatar
    Shahid12523
    Icon for Community Champion rankCommunity Champion
    1 year ago

    TopN Brand Group =
    VAR BrandRank = RANKX(ALL('C2 Data'[Brand2]), CALCULATE(SUM('C2 Data'[Volume])), , DESC, Skip)
    RETURN IF(BrandRank <= 5, 'C2 Data'[Brand2], "Others")

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    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.