Ranking a value within a table to other values in the same column differentiated by year and account

[Anonymous]

Hi All, I am having trouble with the RANKX created measure in Power BI. This is what I currently have:

           Rank = RANKX( Filter( All(

           Query1[Year], Query1[Account Name]),

           Query1[Year] = MAX(Query1[Year])),

           CALCULATE(SUM(Query1[Value]

           )))

But based on previous knowledge of the dataset, I know it is not ranking the values correctly. It is spitting out a number that is larger than the amount of data points in the spreadsheet. I included the table below that is named Query1 in my PowerBI file. My goal is to rank the column 'Value' for each account within all values in that column for each year separately. Then, if possible, rank within each year by state. I welcome any and all help. Thank you! 

Year	Account Name	State	Value
2016	ABC Co.	New York	80
2017	ABC Co.	New York	70
2018	ABC Co.	New York	60
2016	1234 Company	Pennsylvania	77
2017	1234 Company	Pennsylvania	68
2018	1234 Company	Pennsylvania	42
2016	Test Account	New York	58
2017	Test Account	New York	91
2018	Test Account	New York	37
2016	XYZ, Inc.	Maine	77
2017	XYZ, Inc.	Maine	93
2018	XYZ, Inc.	Maine	18

[sturlaws]

Hi @Anonymous ,

not quite sure what you are after. If you are looking to create calculated columns, try these two codes:

RankAccountByYears =
RANKX (
    CALCULATETABLE ( 'Table'; ALLEXCEPT ( 'Table'; 'Table'[Year] ) );
    'Table'[Value]
)

RankAccountByYearsAndState =
RANKX (
    CALCULATETABLE ( 'Table'; ALLEXCEPT ( 'Table'; 'Table'[Year]; 'Table'[State] ) );
    'Table'[Value]
)

 Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.