cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

## Dynamic Rank per Product in Time Period

Hi

I need to be help create a Rank column. I need to be able to generate the rank in the context of the Time Period for the Products based on the Amount and This rank will be in the context of the Market where the market acts as a slicer. I added the sample data and the rank. The DAX I created always gives me a rank 1.

 Markets Product Time Period Amt Rank North A Last 52 Weeks 1 1 North B Last 52 Weeks 2 2 North C Last 52 Weeks 3 3 North D Last 52 Weeks 4 4 North E Last 52 Weeks 5 5 North F Last 52 Weeks 6 6 North A Last 13 Week 11 5 North B Last 13 Week 12 4 North C Last 13 Week 4 6 North D Last 13 Week 324 2 North E Last 13 Week 32 3 North F Last 13 Week 3434 1 North A Last 26 Weeks 10 6 North B Last 26 Weeks 34 4 North C Last 26 Weeks 13 5 North D Last 26 Weeks 432432 1 North E Last 26 Weeks 3423 2 North F Last 26 Weeks 124 3 North A Last 4 Weeks 1 5 North B Last 4 Weeks 4 2 North C Last 4 Weeks 7 1 North D Last 4 Weeks 2 4 North E Last 4 Weeks 3 3 North F Last 4 Weeks 0 6

Any Help is appreciated.

10 REPLIES 10
Super User IV

## Re: Dynamic Rank per Product in Time Period

So, are you OK if this is a measure? I have had far more luck with RANKX as a measure versus as a column. You could create two measures:

```MySum = SUM(Table[Amt])

MyRank = RANKX(ALL(Table),[MySum])```

You can create a column like this:

`Column = RANKX(aRanks,[Amt])`

But it won't be grouped like you want. Here is one of the better articles explaining RANKX:

https://www.wiseowl.co.uk/blog/s2469/rankx-function.htm

---------------------------------------

Not the Power BI thought police...

##### I have NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
Super User III

## Re: Dynamic Rank per Product in Time Period

@rampsaladi

GIve this MEASURE a shot as well

```RANK =
RANKX (
FILTER (
ALLSELECTED ( Table1 ),
Table1[Time Period] = SELECTEDVALUE ( Table1[Time Period] )
),
CALCULATE ( SUM ( Table1[Amt] ) ),
,
DESC,
DENSE
)
```
Try my new Power BI game Cross the River
Super User III

## Re: Dynamic Rank per Product in Time Period

@rampsaladi

Infact this one should give the proper results with slicers

```RANK =
RANKX (
CALCULATETABLE (
VALUES ( Table1[Product] ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Time Period] = SELECTEDVALUE ( Table1[Time Period] )
)
),
CALCULATE ( SUM ( Table1[Amt] ) ),
,
DESC,
DENSE
)
```
Try my new Power BI game Cross the River
Frequent Visitor

## Re: Dynamic Rank per Product in Time Period

Thank you for providing the information but the DAX you provided does not give me the correct result, it always gives rank as 1

Super User III

Frequent Visitor

## Re: Dynamic Rank per Product in Time Period

@Zubair_Muhammad

yes I can see that but It does not work when I do it but let me try again.  I also have another requirement where I need to be able to display the Rank for each time period side by side and I tried to do that by filterting the data for the specific time period but it did not work. Any help is appreciated.

Basically I need to show a side by side comparison of the Product Rank over time period in a specific market

Super User III

## Re: Dynamic Rank per Product in Time Period

to show a side by side comparison of the Product Rank over time period in a specific market.....

You can try using this rank MEASURE

```RANK_ =
RANKX (
ALLSELECTED ( Table1[Product] ),
CALCULATE ( SUM ( Table1[Amt] ) ),
,
DESC,
DENSE
)
```

Try my new Power BI game Cross the River
Super User III

## Re: Dynamic Rank per Product in Time Period

@rampsaladi

Use a Matrix Table...Put Time Period in Columns..Products in Rows and RANK measure in values

Try my new Power BI game Cross the River
Frequent Visitor

## Re: Dynamic Rank per Product in Time Period

This is really helpful you know I never thought of it, but I have one more request can I actually have a different rank measure for each Time Period, the only reason i ask it I have other measure values that I need to dsiplay along with Rank and if I add them its get little bit skewed. the look. So Ideally I would like to have a Rank Measure for Say latest 13 Weeks timeperiod data and this will change based on the Market Slicer

## Helpful resources

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors