Forum Discussion

ss1888's avatar
ss1888
Regular Visitor
9 years ago
Solved

top 10 rows put in table with filter

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's avatar
    v-yulgu-msft
    9 years ago

    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

3 Replies

  • Thiyags's avatar
    Thiyags
    Icon for Helper II rankHelper II
    9 years ago

    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

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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's avatar
      pratkat
      Frequent Visitor
      9 years ago

      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