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
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors