TOP 10 FILTER- CHANGE COLUMNS

[Anonymous]

I have this table which is filtered by the 10  customers with the higher sales as you can see below. I used the filter optio TOP N But now I need this visual to show me the top 10 customers starting the most recent year.  so 2021. not 2019.

Adittional to this i have a time slicer that allows me to choose the range of years I want to see. In this case i chose 2019 to 2021. I need though that the top 10 customers are arranged based on year 2021. Do you know how to do it please?

The customers are filtered on the left by the top 10, but it follows only 2019. I need the filter to affect the most recent year so 2021 ( i also have a ltime slicer that shows a range of years.

[Icey · Accepted solution]

Hi @Anonymous ,

Try to create a measure like below and set it as "is 1" in "Filters on this visual" field. (I filter top 3 due to my small sample.)

Measure = 
VAR MaxYear_ =
    CALCULATE ( MAX ( 'Table'[Year] ), ALLSELECTED ( 'Table'[Year] ) )
VAR Category_ =
    SUMMARIZE (
        TOPN (
            3, ----------change it to 10 in your scenario
            SUMMARIZE (
                FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year] = MaxYear_ ),
                'Table'[Category],
                "SumValue_", SUM ( 'Table'[Value] )
            ),
            [SumValue_], DESC
        ),
        [Category]
    )
RETURN
    IF ( MAX ( 'Table'[Category] ) IN Category_, 1 )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Create a measure for most recent year and top 10 on that and use that as filter 

sales =
var _max = Year(maxx(allselected('Date'),'Date'[Date]))
return
calculate(sum(Table[sales amount]) , filter('Date', 'Date'[Year] =_max))

Top10 = calculate(sum(Table1[sales]),TOPN(10,allselected(table[Customer]),sum(Table1[sales]),DESC), values(table[Customer]))

Use top 10 as visual level filter