Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@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

Anonymous
Not applicable


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

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors