Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a column called Time Period which is a string. I have a list of merit rankings for a student which is a whole number. How can I get a simple column which gives me the difference from one time period to the next as shown below? I have looked at some solutions that use an index column but I don't have this. As I said - Time Period is a string, not a date.
This data comes from a table where I have used a slicer to filter for a particular student id. I want to see the changes in a particular student's merit ranking over periods of time. Please see below for a link to all the data in the table if this is needed
Time Period | Merit Ranking | Diff |
2015 (T2) | 47 | |
2015 (T4) | 51 | 4 |
2016 (T2) | 35 | -16 |
2016 (T3) | 44 | 9 |
2016 (T4) | 47 | 3 |
2017 (T2) | 79 | 32 |
2017 (T3) | 42 | -37 |
2017 (T4) | 50 | 8 |
2018 (T2) | 25 | -25 |
Link to full data - Change in Merit Ranking over time.xlsx
Any help would be appreciated. Thanks.
Solved! Go to Solution.
Hi @dphillips,
I have modified the Measure, you could refer to below measure:
Measure = RANKX(ALLEXCEPT('Max of Merit Ranking by Time Pe','Max of Merit Ranking by Time Pe'[studentid]),FIRSTNONBLANK('Max of Merit Ranking by Time Pe'[Time Period],'Max of Merit Ranking by Time Pe'[Time Period]),,ASC,Dense)
Result:
You could download the pbix file to have a test.
Regards,
Daniel He
Hi @dphillips,
Based on my test, you could refer to below measures:
Measure = RANKX(ALL('Max of Merit Ranking by Time Pe'),FIRSTNONBLANK('Max of Merit Ranking by Time Pe'[Time Period],'Max of Merit Ranking by Time Pe'[Time Period]),,ASC,Dense)
Measure 2 = var a=[Measure]-1 return CALCULATE(SUM('Max of Merit Ranking by Time Pe'[Merit Ranking]))-CALCULATE(SUM('Max of Merit Ranking by Time Pe'[Merit Ranking]),FILTER(ALLEXCEPT('Max of Merit Ranking by Time Pe','Max of Merit Ranking by Time Pe'[studentid]),'Max of Merit Ranking by Time Pe'[Measure]=a))
Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
Hi @v-danhe-msft. Looking at your Measure 2 it looks like this will not work. Note where you skip a rank, then measure 2 has a value of 92 which is clearly not the difference between 91 and 92. This obvioulsy has something to do with the fact that there are gaps from 2016 (T2) to 2016 (T4). Is there any way of fixing this up at all?
Hi @dphillips,
I have modified the Measure, you could refer to below measure:
Measure = RANKX(ALLEXCEPT('Max of Merit Ranking by Time Pe','Max of Merit Ranking by Time Pe'[studentid]),FIRSTNONBLANK('Max of Merit Ranking by Time Pe'[Time Period],'Max of Merit Ranking by Time Pe'[Time Period]),,ASC,Dense)
Result:
You could download the pbix file to have a test.
Regards,
Daniel He
That seems to work well @v-danhe-msft. Just one final thing - is it possible to remove the first value eg 91 for measure 2 as I would only like to see the change and not the actual value for the first time period. Is there a way of making this blank? Thanks so much for your help so far.
@v-danhe-msft. I adjust things slightly to remove the first row for Measure 2 which was the actual result, not the difference.
Measure 2 = var a=[Measure]-1 return IF([Measure] = 2,BLANK(),CALCULATE(SUM('Results'[Merit Ranking]))-CALCULATE(SUM('Results'[Merit Ranking]),FILTER(ALLEXCEPT('Results','Results'[studentid]),'Results'[Measure]=a)))
Things seems to work as I wanted it to now. Thanks so much for your help
@v-danhe-msft Spoke too soon. The data I gave you did not have a number of other columns in it. When I use the measure with all the other data, it is very slow to calculate and comes up with the wrong data. Is there any way to take into account and filter out other columns?
Hi dphillips,
You should to use Power Query in order to order your data.
First by the "studentid" column and second by the "Time Period" column.
Later creat an "index" column.
Finally, creat a measure as show it in image I've attached.
Tell us if works for you.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |