Rank a Running Total

[Anonymous]

I'm trying to create a measure that will rank a running total (that's also a measure).

To explain my data, I have the following (It may help to imagine Formula 1 here as my data has a similar structure):

Seasons (with a start and end date, which lasts for 6 months)

Tournaments (including a date of each tournament, and a FK field to Seasons)

Results (including Player, their Result e.g. 1st, 2nd, Their Points awarded e.g. 16 for 1st, 12 for 2nd..., a FK field to Tournaments)

My running total measure is as follows:

PointsRunningTotal = SUMX(FILTER(ALLSELECTED(Tnmt),AND(Tnmt[Week]<=MAX(Tnmt[Week]),Tnmt[Week]>=LOOKUPVALUE(Seasons[First Week],Seasons[Season],Tnmt[Season]))),[Pts])

It creates a running total but confined to each season, i.e. the running total resets on the 1st week of the new season. A few other notes my Tournament table is called "Tnmt" for short and the Pts measure is a Sum of the Points column in the Results table.

This works perfectly when placing it on a matrix visual (Player is the rows, Tournament Date is the column, and there's a page filter that restricts the page to a specific season):

What I want to do is use the rank formula to effectively create their league position and put this into a matrix visual. So looking at the two players highlighted in yellow (beignning with a C & H), I'd want amatrix visual to display their rank as follows:

Their ranks should be as follows (which is their league position on the identified dates):

Week: 11th Jul - 18th Jul - 25th Jul

C: - - - - 2 - - - - - - 1 - - - - - -2

H: - - - - 1 - - - - - - 2 - - - - - -1

I've been unable to produce a measure that generates any sensible results.

[amitchandak]

See if this can help

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

[Anonymous]

Hi @amitchandak , Unfortunately this hasn't helped - it hasn't gone into elements like running totals, and I think part of my problem is the matrix visual too.

Here's my current problem with some dummy data... I'm using the measure...

RankPointsRunningTotal = RANKX(Results,[PointsRunningTotal],,0,skip)

This is producing the following output:

I have a slicer that is filtering the data to just a specific season, containing the 3 weeks in January being displayed (there are also two other tournaments in 2 other seasons which are being successfully filtered out of this page - and out of the measures). The odd thing here is the "Player" Nico Rosberg didn't play in the season being shown. The top visualisation is filtering him out as I'd expect, but the rank visualisation is including him (which it shouldn't). And the Rank's aren't working either. I'm expecting the ouput of the Rank visualisation to look like what's in the spreadsheet.

Here's a link to the file above: https://www.dropbox.com/s/wryo92u92k80dy2/CumulativeRankProblem.pbix?dl=0