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 depen...
  • 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

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft 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