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
dphillips
Helper IV
Helper IV

Change in Merit Ranking over time

I have some data on students showing their merit ranking over time. The time is not an actual time or date but a reporting period. The reporting periods need to be in a specific order so I have created a table which holds the Result Group with a sort order column in it and I have linked that table to the main data table using the Result Group.

Change in Merit Rankings.png

No problems just showing the merit ranking for each reporting period as shown in the image. What I want now is a graph showing the change in merit ranking from one reporting period to the next. For example, with the student highlighted here, the merit ranking has changed by -4 from the reporting period PR_Yr9_1 to PR_Yr9_2. Then it has increased by 11, then -10, -9, 24, -32, -11 and finally 17. Am having trouble getting a measure to use here that gives me the change in merit ranking from one period to the next. Obviously, if it is the first reporting period, there is no change so the change in merit would be 0.

 

Would love any help in putting together this measure to do this. Thanks for any help.

 

Please see attached .pbix. 

Change in Merit Ranking over time

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

@dphillips ,

 

So your requirement is to rank the difference in each FY based on your Result Group and Student ID, right? I assume the difference is calculated based on sort column in your sample data(Not sure because I can't access the redshift database). Maybe you could create two measures as pattern below:

Difference = 
VAR Current_Sort = MAX('Result Group Table'[Sort Order])
VAR Next_Sort = Current_Sort + 1
VAR Current_FY_Sum  = CALCULATE(SUM(uncRedshift_Studentresults[MeritRanking]), FILTER('Result Group Table', 'Result Group Table'[Sort Order] = Current_Sort))
VAR Next_FY_Sum = CALCULATE(SUM(uncRedshift_Studentresults[MeritRanking]), FILTER('Result Group Table', 'Result Group Table'[Sort Order] = Next_Sort))
RETURN
IF(Current_Sort = 1, 0, Next_Sort - Current_Sort)
Rank_ = RANKX(ALL(uncRedshift_Studentresults), [Difference], , ASC, Dense)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for your response @v-yuta-msft . I didn't want to rank anything. All I wanted was the change in value from one reporting period to the next. In the example above I want to be able to create a graph that looks like this.

Change in Merit Rankings 2.png

 

For each reporting period, what is the difference when compared to the previous reporting period?

Hope this makes sense.

 

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.