Rank seasons points across all seasons.

[Anonymous]

Hi all,

I have a simple model of English Premier Legaue games.

A fact table with a line per game.

Dimensions a Calendar - with all dates and a season attribute.

At the  moment I have a visualisation of a table as below and use

Season Rank =

If(HASONEVALUE('Home Team'[Team]),RANKX(ALL('Home Team'[Team]), [Total Point per Team]), Blank())

How do I get overall rank? Thanks

Season		Points		Season Rank		Team		Overall Rank
2017 -18		100		1		Man City		?      1
2018 - 19		98		1		Man City		?2
2018 - 19		97		2		Liverpool		?3
2016 - 17		93		1		Chelsea		?4

[jdbuchanan71]

Hello @Anonymous 

Give this a try.

Overall Rank = 
VAR _table = 
CALCULATETABLE(
    SUMMARIZE('Home Team','Home Team'[Team],Dates[Season]),ALL(Dates),ALL('Home Team'))
RETURN
If(HASONEVALUE('Home Team'[Team]),RANKX(_table, [Total Point per Team]), Blank())

[Anonymous]

Thanks  - I got this error though

I also tried 

CALCULATETABLE(

ADDCOLUMNS(CROSSJOIN(ALL('Home Team'[Team]),ALL('Date'[Season])), "Points" , [Total Point per Team]),ALL('Date'),ALL('Home Team'))

which does produce at least a table in the model with the summarized data. Any help greatly appreciated. Could it be my measure works differantly to a simple sum?  Thanks

[jdbuchanan71]

I think that is because the Date table is not joined to the Home Team table.  Try this.

Overall Rank = 
VAR _table = 
CALCULATETABLE(
    SUMMARIZE('Fixtures','Fixtures'[Team],Dates[Season]),ALL(Dates),ALL('Fixtures'))
RETURN
If(HASONEVALUE('Home Team'[Team]),RANKX(_table, [Total Point per Team]), Blank())

[Anonymous]

Thanks! Still not quite right. I am getting 1 for all rows now. The other all gave the same as the season rank? Your image sugegsted you gad it working in a model you have?

Thankyou

[jdbuchanan71]

I did but it was just a sample that I discarded.  Can you share your .pbix file?

[Anonymous]

Sorry for the late reply - I only have permissions on onedrive to share with internal or maybe an email address. Are there are alternatives I could use on this site? Thanks very much for your help.

[jdbuchanan71]

You can create a DropBox account for free and put it there then share the link.

[Anonymous]

Thanks hopefully you can  see this .

The table visual is on the last tab , and the last column in the table was the attempt to get the overall rank. Rather than rank per season which works.

Thanks.