Trying To create a raking for FIFA23

[Kuri_190]

Hello all,

Hope this message finds you well and please take into account I'm new to Power BI.

I'm looking at some "fake" FIFA 23 data (provided via Kaggle) and I'm trying to create a table that shows me who are the best players to play at specific position. Every player has a best position to play (with the best overall skill), however they can play anywhere in the field and they have a skill level for every position they can play. For example, if player A is better on a Striker Position, his skill value on that position is 91, however he can also play has a midfielder but his skill will be 78; and so on.

With this information I want to create multiple tables(one for each position) that ranks every player skill value for a particular position, however I'm having a real hard time doing that. I'm trying to use the Rankx and Summarize DAX fucntions, but I tried multiple things. It never works.

Here's a sample of the table that comes from power query:

Player	Best Position	Skill	Skill Value	Positon	Position Value
Mbappe	ST	Pace Total	97	CAM	92
Mbappe	ST	Passing Total	90	CAM	92
Mbappe	ST	Dribbling Total	92	CAM	92
Mbappe	ST	Pace Total	97	CB	57
Mbappe	ST	Passing Total	90	CB	57
Mbappe	ST	Dribbling Total	92	CB	57
Messi	CAM	Pace Total	81	CAM	91
Messi	CAM	Passing Total	90	CAM	91
Messi	CAM	Dribbling Total	94	CAM	91
Messi	CAM	Pace Total	81	CB	53
Messi	CAM	Passing Total	90	CB	53
Messi	CAM	Dribbling Total	94	CB	53

Here's what I'm looking for:

Player	Position	Positon Value	Ranking
Mbappe	CAM	92	1
Messi	CAM	91	2
Mbappe	CB	57	1
Messi	CB	53	2

After getting a table with this, I can filter with the position on the visual side of the final table.

Here's my current formula for rankx

Rank Player =

CALCULATE(

    RANKX (

        SUMMARIZE(

            players_fifa23,

            players_fifa23[Name],

            players_fifa23[Position],

            players_fifa23[Position Value]

        ),

        players_fifa23[Position Value]

    )

)

Here's the error that shows on Power BI:

A single value for column 'Position Value' in table 'players_fifa23' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

I've used average for this, but the value that I get is the average of all players for every position and the rank is 1 for all the players.

Maybe this is a simple question and I might have missed something on the fundamentals, but I'm not being able to get the desired result. Thanks for reading!

[Anonymous · Accepted solution]

Hi @Kuri_190 ,

Please try below steps:

1. create a measure with below dax formula

Ranking =
VAR cur_position =
    SELECTEDVALUE ( 'Table'[Positon] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Positon] = cur_position )
RETURN
    RANKX ( tmp, CALCULATE ( MAX ( 'Table'[Position Value] ) ),,, DENSE )

2. add a table visual with measure and fields

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.