cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Top 3 Value for each category and year

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/

1 ACCEPTED SOLUTION
Community Support

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

7 REPLIES 7
Anonymous
Not applicable

Did You Try This Measure?
Rank within Area =

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

Community Support

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
Not applicable

Hi, @Wanderer1985  Please Try This Measure.

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

New Member

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

Anonymous
Not applicable

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

Super User

@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)

New Member

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 🙂

Announcements

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors