I want to show a 1-20 column that stays the same, only the teams move up/down. . So Arsenal in the screenshot is 1 whereas Sunderland here would be 20. How would I go about composing this column?

Ah, got it now 😄. If you just need a fixed 1-20 column regardless of points or position, the simplest option is to create an Index Column in Power Query. Transform Data → Add Column → Index Column → From 1 That will give you a permanent 1-20 numbering column that stays the same, while the team names and stats can change each gameweek.

How to make a 1-20 column | Microsoft Fabric Community

5 Replies

WheelchairWilly
Helper III
1 month ago
That didnt work Shar. I would point out that because the points will change every week so dont worry about the teams position etc. I just want a column before GW which shows 1 right trrough to 20 down that column.
- ShahRukhSameer
  Continued Contributor
  1 month ago
  Ah, got it now 😄. If you just need a fixed 1-20 column regardless of points or position, the simplest option is to create an Index Column in Power Query.
  
  Transform Data → Add Column → Index Column → From 1
  
  That will give you a permanent 1-20 numbering column that stays the same, while the team names and stats can change each gameweek.
- Ashish_Mathur
  Super User
  1 month ago
  Hi,
  
  This visual calculation should work
  
  =rownumber(rows)
  
  Hope this helps.
ShahRukhSameer
Continued Contributor
1 month ago
Hi WheelchairWilly,

You can create this as a measure rather than a calculated column. If you want the teams to move up/down based on their Points, use:
Position =
RANKX(
ALLSELECTED('Teams'[Home Team]),
[Points],
,
DESC,
DENSE
)

Then add the Position measure to your table.

If you also need Goal Difference and other league rules to break ties, you can include those in the ranking logic as well.
aswathimohan
Advocate I
1 month ago
Hi WheelchairWilly ,

For this, you can use a Visual Calculation to get the row number as follows

Click on the 3 dots that appear at the right corner when you hover over your table visual > New Visual Calculation > Custom

Something like this will appear
You can type in

Row = ROWNUMBER(ORDERBY([Points],DESC,[Home Team],ASC))
It will come like this
The values will have decimal points. To show as Integers, click on the Visual > Format Pane> Specific Column. Select Row (or whatever name you have given for the visual Calculation) in "Apply Settings to" Option. In Values, Put 0 as Value in decimal places.
If you want the row number to come at the beginning, simple go to build plane, and drag the row field to the top in the columns section

See if this works