A Measure to Rank the Previous Academic Year

[Anonymous]

Hello,

I created the following measure that ranks schools by proficieny and it works:

Rank_Schools = RANKX( ALL( SchoolScores[SchoolName] ), [ProficientPct],,,Dense)

Now I need to add columns to the table visual that show the rank and proficiency that each school had in the previous academic year.

For example:

School Name	Current Rank	Current % Proficient	Previous Rank	Previous % Proficient
School A	1	90%	2	80%
School B	2	80%	3	70%
School C	3	70%	1	90%

Current and previous are academic years.  So if the user filters AcademicYear to 2022-23, then it would be current and 2021-22 would be the previous.  Note that some years were skipped during the pandemic.  (for example there is no data in the table for 2019-20 and there is only data for some schools in 2020-21)

I can add a date field for school year end date, if that will make creating these two measures easier.  (2023-06-30, 2022-06-30, etc.)

Thank you,

J

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your dataset.

All measures are in the sample pbix file.

[Anonymous]

@Jihwan_Kim ,

Thank you so much for the example.  I added a whole number calculated column called year to my table.  My previous percent proficient measure has no errors, but it displays no value when I add it to the table.  Here are my measures:

ProficientPct = 
    ROUND ( 
        DIVIDE ( 
            SUM ( SchoolScores[L3_L4_Count] ),
            SUM ( SchoolScores[Total_Tested] ) 
        ),
        2 
    )

ProficientPctPrev = 
    CALCULATE ( 
        [ProficientPct], 
        SchoolScores[Year] = MAX ( SchoolScores[Year] ) - 1 
    )

Do you know why the ProficientPctPrev measure would be blank?

Thanks,

J

[daXtreme]

@Anonymous 

Without seeing your model it's hardly possible to tell you why you get blanks. The only general thing that comes to mind is that you have put some other filters in place that combined with the filter under CALCULATE yield an empty filter. Please bear in mind that if you have a column in your table(s) that is sorted by another column, this can easily happen and you'll be scratching your head because to diagnose this you'll have to take a look at the DAX that the visual generates.

[Anonymous]

Thank you @daXtreme ,

This visual is based on a single table (a SQL view) with the following fields:

Academic Year *

Year (created this numeric column in order to be able to -1)

School *

Subject *

Grade *

L3_L4_Count

Total Tested

The results can be filtered on any column with an asterisk.  Does that mean that my ProficientPercentPrev measure needs to be altered?

Thanks,

J

[daXtreme]

Hi @Anonymous 

Creating a model with just a single table is... well, bad practice and should never be employed in production because of the myriads of problems that will stem from that in no time. The main reason for this is something called "an auto-exist optimization." In fact, it's not really as much an optimization as it is a bug that Microsoft will not fix (for reasons that would be hard to state here in full). Please, if you want to stay sane, change your model into a proper star schema to eradicate all the problems that one-table models are fraught with. But, of course, it's up to you. However, if you don't do this, there'll most likely be issues that will force you into doing that anyway. So, why wait to do it correctly?

Here's an article that all people that deal with PBI should read early on in their career: Understand star schema and the importance for Power BI … (bing.com)