Dynamic Top N + Other

[dgilm33]

Hi All,

I have created a trending line graph visual, with quarter year and month year in the X-axis, the spend in the Y-axis, and then of course the legend. I also have slicers on the page. I've been asked a few times now to have the visual be "top n + other". To this point I haven't been able to accomplish this. I can't share the data I'm working with, but have created this sample data below. Using this data, I would for example want to be able to show Top 2 + Other in the line graph where it will show Ford and Toyota, with Dodge and Tesla making up the "Other" spend line.

[Anonymous · Accepted solution]

Hi @dgilm33 

You can refer to the following solution. 

Sample data is the same as yours.

1.Create new table

Type = var a=SUMMARIZE('Table',[Brand])
return UNION(a,{"Other"})

The table has no relationship with the original table.

2.Creat a measure.

MEASURE =
VAR a =
    SUMMARIZE (
        TOPN (
            2,
            SUMMARIZE ( ALLSELECTED ( 'Table' ), [Brand], "Sum", SUM ( 'Table'[Sales] ) ),
            [Sum], DESC
        ),
        [Brand]
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Type'[Brand] ) = "Other",
        CALCULATE ( SUM ( 'Table'[Sales] ), NOT ( 'Table'[Brand] ) IN a ),
        CALCULATE (
            SUM ( 'Table'[Sales] ),
            'Table'[Brand] IN a,
            'Table'[Brand] IN VALUES ( 'Type'[Brand] )
        )
    )

3.Put the following field to visual.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vicky_]

Read this: https://www.sqlbi.com/articles/filtering-the-top-products-alongside-the-other-products-in-power-bi/