Forum Discussion

dgilm33's avatar
dgilm33
Helper I
2 years ago
Solved

Dynamic Top N + Other

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.