Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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:
Please help.
Thanks in advance 🙂
@Anonymous
Solved! Go to Solution.
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,
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,
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.
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
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 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 __rankThis 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 __diffIf 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.
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |