cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ss1888
Regular Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft
Microsoft

Re: top 10 rows put in table with filter

Hi @ss1888,

 

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

 

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.

2.PNG

 

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

 

Thanks,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Thiyags Helper II
Helper II

Re: top 10 rows put in table with filter

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

Microsoft
Microsoft

Re: top 10 rows put in table with filter

Hi @ss1888,

 

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

 

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.

2.PNG

 

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

 

Thanks,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

pratkat
Frequent Visitor

Re: top 10 rows put in table with filter

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

 

 

 

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors