cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rampsaladi Regular Visitor
Regular 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.

 

MarketsProductTime PeriodAmtRank
NorthALast 52 Weeks11
NorthBLast 52 Weeks22
NorthCLast 52 Weeks33
NorthDLast 52 Weeks44
NorthELast 52 Weeks55
NorthFLast 52 Weeks66
NorthALast 13 Week115
NorthBLast 13 Week124
NorthCLast 13 Week46
NorthDLast 13 Week3242
NorthELast 13 Week323
NorthFLast 13 Week34341
NorthALast 26 Weeks106
NorthBLast 26 Weeks344
NorthCLast 26 Weeks135
NorthDLast 26 Weeks4324321
NorthELast 26 Weeks34232
NorthFLast 26 Weeks1243
NorthALast 4 Weeks15
NorthBLast 4 Weeks42
NorthCLast 4 Weeks71
NorthDLast 4 Weeks24
NorthELast 4 Weeks33
NorthFLast 4 Weeks06

 

Any Help is appreciated.

10 REPLIES 10
Super User
Super User

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 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User
Super User

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
)
Super User
Super User

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
)
rampsaladi Regular Visitor
Regular 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
Super User

Re: Dynamic Rank per Product in Time Period

rampsaladi Regular Visitor
Regular 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
Super User

Re: Dynamic Rank per Product in Time Period

Hi @rampsaladi

 

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
)

 

Super User
Super User

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

 

rankXY.png

 

 

rampsaladi Regular Visitor
Regular 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
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 59 members 1,232 guests
Please welcome our newest community members: