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

Difference in ranking of person during different years

Hi Everybody,

 

I am very much new to some of the power of power BI, so need your help for one of the issue I am facing.

 

I have a table with column:

Person, Year.

 

And Marks is calculated as measure using some other columns in table.

 

I have created measure to calculate Rank of Person (based on marks he gets) within that year using the DAX:

RANKX(All('table'[Person]), [Marks])

 

It worked fine and result is as in below picture:

Rank_Marks.PNG

 

Now I want to see how much down or up that person go in ranking from one year to another (Basically only to check for 2017 to 2018 rank changes where 2018 being the latest year).

 

So I want DAX to get difference between rannks of Person from 2017 to 2018 and desired result should look like:

RankDiffIssue.PNG

 

 

Please help.

Thanks in advance 🙂

@Anonymous 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

This is your rank COLUMN (not a measure):

 

Rank = 
var __currentMarks = Ranking[Marks]
var __rank =
    CALCULATE(
        RANKX(
            SUMMARIZE(
                Ranking,
                Ranking[Marks],
                Ranking[Person]
            ),
            Ranking[Marks],
            __currentMarks,,Dense
        ),
        ALLEXCEPT( 
            Ranking,
            Ranking[Year]
        )
    )
return
    __rank

This is your Rank Diff column:

Rank Diff YoY = 
var __currentRank = Ranking[Rank]
var __currentYear = Ranking[Year]
var __prevYearRank =
    CALCULATE(
        VALUES( Ranking[Rank] ),
        Ranking[Year] = __currentYear - 1,
        ALLEXCEPT( Ranking, Ranking[Person] )
    )
var __diff = __currentRank - __prevYearRank
return
    __diff

If you don't want to show anything for the first year, then you should change this to:

Rank Diff YoY = 
var __currentRank = Ranking[Rank]
var __currentYear = Ranking[Year]
var __prevYearRank =
    CALCULATE(
        VALUES( Ranking[Rank] ),
        Ranking[Year] = __currentYear - 1,
        ALLEXCEPT( Ranking, Ranking[Person] )
    )
var __diff = __currentRank - __prevYearRank
return
    if( __prevYearRank <> BLANK(), __diff  )

 

Best

Darek

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Hi,

 

Can you share a .pbix file with some data to work on it? (Also, with the necessary data model which you are working on)

 

Thanks,

Anonymous
Not applicable

Mate, I don't think you're creating measures. You're creating calculated columns. Please state clearly whether you need a calculated column or a measure. These are 2 very different things and I really hope you know the difference.

 

Best

Darek

@Anonymous If you can let me know how to create calculated column for ranking at the first place then it might be easier to get the difference as required??

 

I have tried to create calculated column to get ranks but failed to do thats why I go for creating Rank as measure. But if you can help me in providing DAX for rank as calculated column it will be really helpful as well as knowledgeable.

 

Thanks in advance.

Anonymous
Not applicable

By the way, I think you should treat two persons with the same Marks within the same year as the SAME RANK. This is exactly what my code does.

 

Best

Darek

Anonymous
Not applicable

This is your rank COLUMN (not a measure):

 

Rank = 
var __currentMarks = Ranking[Marks]
var __rank =
    CALCULATE(
        RANKX(
            SUMMARIZE(
                Ranking,
                Ranking[Marks],
                Ranking[Person]
            ),
            Ranking[Marks],
            __currentMarks,,Dense
        ),
        ALLEXCEPT( 
            Ranking,
            Ranking[Year]
        )
    )
return
    __rank

This is your Rank Diff column:

Rank Diff YoY = 
var __currentRank = Ranking[Rank]
var __currentYear = Ranking[Year]
var __prevYearRank =
    CALCULATE(
        VALUES( Ranking[Rank] ),
        Ranking[Year] = __currentYear - 1,
        ALLEXCEPT( Ranking, Ranking[Person] )
    )
var __diff = __currentRank - __prevYearRank
return
    __diff

If you don't want to show anything for the first year, then you should change this to:

Rank Diff YoY = 
var __currentRank = Ranking[Rank]
var __currentYear = Ranking[Year]
var __prevYearRank =
    CALCULATE(
        VALUES( Ranking[Rank] ),
        Ranking[Year] = __currentYear - 1,
        ALLEXCEPT( Ranking, Ranking[Person] )
    )
var __diff = __currentRank - __prevYearRank
return
    if( __prevYearRank <> BLANK(), __diff  )

 

Best

Darek

View solution in original post


@Anonymous wrote:

This is your rank COLUMN (not a measure):

 

Rank = 
var __currentMarks = Ranking[Marks]
var __rank =
    CALCULATE(
        RANKX(
            SUMMARIZE(
                Ranking,
                Ranking[Marks],
                Ranking[Person]
            ),
            Ranking[Marks],
            __currentMarks,,Dense
        ),
        ALLEXCEPT( 
            Ranking,
            Ranking[Year]
        )
    )
return
    __rank

This is your Rank Diff column:

Rank Diff YoY = 
var __currentRank = Ranking[Rank]
var __currentYear = Ranking[Year]
var __prevYearRank =
    CALCULATE(
        VALUES( Ranking[Rank] ),
        Ranking[Year] = __currentYear - 1,
        ALLEXCEPT( Ranking, Ranking[Person] )
    )
var __diff = __currentRank - __prevYearRank
return
    __diff

If you don't want to show anything for the first year, then you should change this to:

Rank Diff YoY = 
var __currentRank = Ranking[Rank]
var __currentYear = Ranking[Year]
var __prevYearRank =
    CALCULATE(
        VALUES( Ranking[Rank] ),
        Ranking[Year] = __currentYear - 1,
        ALLEXCEPT( Ranking, Ranking[Person] )
    )
var __diff = __currentRank - __prevYearRank
return
    if( __prevYearRank <> BLANK(), __diff  )

 

Best

Darek


Hi @Anonymous 

Thank you so much!! It works 🙂

@Anonymous I am creating measure only. This pic I have shown you in excel that how the result looks like when i showed the power bi result in table form of visual.

 

And , measure or calculated column anything would be fine as a solution but my ranking is a measure.

 

Thanks.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (764)