Top 3 Value for each category and year

[Wanderer1985]

Hi im trying to create a Top 3 measure (rank) for each Year and Area. Is it possible to create a measure that rank the value based on dimension value area and year like:

I have already tried this post, and cant get it to work in my scenario:

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

[Anonymous]

Did You Try This Measure? 
Rank within Area =

      
RANKX (
ALLEXCEPT ( Table1, Table1[Area] ),
CALCULATE ( SUM ( Table1[Value] ) )
)

[Anonymous · Accepted solution]

Hi  @Wanderer1985 ,

Here are the steps you can follow：

1. Create measure.

rank =
RANKX(
    FILTER(ALL('Table'),'Table'[Area]=MAX('Table'[Area])&&'Table'[Year]=MAX('Table'[Year])),
    CALCULATE(SUM('Table'[Value])),,DESC)

2. Result:

Best Regards,

Liu Yang

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

[Anonymous]

Hi, @Wanderer1985  Please Try This Measure.

Rankx =
RANKX (
FILTER (
ALL ( 'Table '[Area], 'Table '[Year] ),
'Table'[Area] = MAX ( 'Table'[Area] )
),
CALCULATE ( SUM ( 'Table'[Value] ) )
)

[Wanderer1985]

Hi, this also only gives 1 in return... 

[Anonymous]

ok, Try This Measure.

Rank within Area =
RANKX (
    ALLEXCEPT ( Table1, Table1[Area] ),
    CALCULATE ( SUM ( Table1[Value] ) )
)

I Try This Measure in your file and it's working. if your got a solution please mark this as a solution

[amitchandak]

@Wanderer1985 , Create a simple TOP 3 and use that

Top 3= calculate(max(Table[Year]),TOPN(3,allselected(table[Year]),calculate(sum(Table[Value])),DESC), values(table[Year]))

or create a rank measure and filter at visual level for <=3
Rankx(allselected(table[Year]), calculate(sum(Table[Value])),,desc)

or

Rankx(filter(summarize(allselected(table),Table[Year], table[Area]), [Area] =max([Area])), calculate(sum(Table[Value])),,desc)

[Wanderer1985]

Hi amitchandak.

I have tried all 3 solutions now - and do not get the expected result:

I have added the PBIX file with the example if you would like to have a look at the scenario 🙂

top 3 ex