top 10 rows put in table with filter

[ss1888]

Hello all I wonder if someone can help me.

I have a table of data which I would like to pull out the top 5 rows depending on a particular value. I would like this to then change dynamically depending on the slicer the user applies.

I have managed to create a new table which pulls out the top 5 records but I cannot get this to then update depending on the slicer I select,. The data stays static.

Any advice would be fantastic

[v-yulgu-msft · Accepted solution]

Hi @ss1888,

I have tested this scenario using below sample data (I added an index column first). Please refer to the workarounds.

Create two measures.

category_ =
IF (
    CONTAINS (
        TOPN ( 5, ALLSELECTED ( 'Top 10' ), 'Top 10'[Value], DESC ),
        'Top 10'[Index], MAX ( 'Top 10'[Index] )
    ),
    LASTNONBLANK ( 'Top 10'[Category], "" ),
    BLANK ()
)

Value_ =
IF (
    CONTAINS (
        TOPN ( 5, ALLSELECTED ( 'Top 10' ), 'Top 10'[Value], DESC ),
        'Top 10'[Index], MAX ( 'Top 10'[Index] )
    ),
    SUM ( 'Top 10'[Value] ),
    BLANK ()
)

Add column [Index], measure [category_] and measure [Value_] into a table visual.

If you have any question, please feel free to ask.

Thanks,
Yuliana Gu

[pratkat]

Hi @v-yulgu-msft,

Need your help in solving this - Display top 5 values based on three slicers ( Collection, Region and Month) and the data is in Table visualization.

Based on the slicer selection it has to display top 5 Revenue values in Table visual.

I tried creating Index columns and other measures and it didn't work for me. Appreciate your help!

Thank you!

Kate

[Thiyags]

Can you provide some sample data.

Approach is to create a Rank measure to calculate the rank for the rows depends on the selection.

Put the rank measure in the slicer