Project Report - Weighted Ranking Query

[MarySt4]

I am creating a project report listing current projects.  I am having trouble creating a query which lists the weighted ranking based of the data displayed in the 'Priority' and 'Benefit & Effort Score' columns.

The Priority allowed values are High, Medium, and Low. The Benefit & Effort Score value range from 1.0 to 10.0.

The expected report data for the Weighted Ranking allows users to see the Top 10 projects based on a combined high Priority and Highest Benefit & Effort score etc. 

I am not sure of next steps to complete this query.

displayname=Weighted Ranking
textmode=true
valueexpression=IF({priority}="Medium","5")
valueformat=HTML

Thank you!

[Madalyn_Destafney · Accepted solution]

I wonder if you can make this easier on yourself without text mode while achieving the same result. Perhaps utilize filters more to pull only projects with certain priorities AND scores, and that filter combination essentially gives these projects weight. For example with just 1 score example, of course you can replace with any score-related filters:

Once you filter these in, you could sort the report columns by the next level down you want to see, like project planned completion date, for example. Thoughts?

[MarySt4]

Thank you so much Madalyn for the recommendation.  It was really helpful!