cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jessfarber
New Member

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

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! 

 

YearAccount NameStateValue
2016ABC Co.New York80
2017ABC Co.New York70
2018ABC Co.New York60
20161234 CompanyPennsylvania77
20171234 CompanyPennsylvania68
20181234 CompanyPennsylvania42
2016Test AccountNew York58
2017Test AccountNew York91
2018Test AccountNew York37
2016XYZ, Inc.Maine77
2017XYZ, Inc.Maine93
2018XYZ, Inc.Maine18
1 REPLY 1
sturlaws
Resident Rockstar
Resident Rockstar

Hi @Jessfarber ,

 

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors