Top 10 or Top all not working as expected

[Juramirez]

Hi all 

I'm having these formulas to show Top10/ Top all: 

Top= RANKX(FILTER ( ALL ( 'Table A ), 'Table A'[FilteredField] = "6" ),[AMOUNT],,DESC)

Top/All = IF([Top]<=10,"Top 10","All")

When I select top all it shows all values ignoring the condition of the filter (in this case it shows top 10 values when FilteredField is equal to 6 but when selecting all, it shows values with FilteredField equal to 1,2,3,...)

What i'm doing wrong here?

Regards

J.

[v-yulgu-msft]

Hi @Juramirez,

Have you tried this?

Top/All = IF(RANKX(FILTER ( ALL ( 'Table A ), 'Table A'[FilteredField] = "6" ),[AMOUNT],,DESC)<=10,"Top 10","All")

What do you mean "select top all"? Select from a slicer? Please provide some sample data and show us your desired output.

Regards,

Yuliana Gu

[Juramirez]

Hi @v-yulgu-msft

Here you can see sample data

What I want is to filter all data by column ParameterToFilter=6. Then I want to show a top 10 of these filtered values that have the higher Amount but i want to have a Slicer which allows me to show All (not just top 10) values that meet this filter. 

Regards

Julian

[Anonymous]

Where is the excel file?

[Juramirez]

I just update the post. It's in Google Drive.

[Juramirez]

Any update about this? 

Any help is appreciated.

Regards,

J.

[v-yulgu-msft · Accepted solution]

Hi @Juramirez,

Please try below measures:

sum amount =
CALCULATE (
    SUM ( Table1[Amount] ),
    FILTER ( Table1, Table1[ParameterToFilter] = 6 )
)

Rank = RANKX(ALLSELECTED(Table1),[sum amount],,DESC,Dense)

flag = IF(SELECTEDVALUE(Table2[Selection])="All",1,IF([Rank]<=5,1,0))

Table2 is an extra table generated manually which provides slicer selection. Column [ParameterToFilter] and measure [flag] should be added to visual level filters.

For details, please refer to the attached .pbix file.

Best regards,

Yuliana Gu