Sorting problem

[PetyrBaelish]

I have a visualisation which is a league table.

The columns in view include Player, Points, Wins, 2nd, 3rd etc.

In the event that Points are tied I have created a calculated column in my table called "PointsForSorting" which is essentially Points + (Wins*0.0001)+(2nd*0.0000001) etc.

The PointsForSorting column is more accurate for sorting purposes as the points column will divide those tied on points alphabetically which is incorrect in this case.

I'm trying to follow the advice via the link below, but it's not working. I select the PointsForScoring field in my fields pane, select my visual, select the modelling tab -> Sort by column, select PointsForScoring but the visual doesn't update. In fact whatever I select under "Sort By Column" doesn't affect the visual.

Could decimilisation be the issue? I've noticed if I drag the PointsForScoring column onto the visual it is set at 2dp (which isn't sufficient)

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

[jthomson]

You need to select the points column and have that sorted by pointsforscoring

[PetyrBaelish]

Unfortunately that's not working for me. I select the Points column from the fields list, select my visualisation, then modelling tab -> sort by column, select the PointsForSorting column but I receive the error message "This column can't be sorted by a column that is already sorted, directly or indirectly, by this column"

I checked the PointsForSorting column (selected it in the fields pane, selected my visualisation -> sort by column) and the PointsForSorting column is sorted by itself (as the default).

I'm assuming the error message is referencing the fact that the PointsForSorting calculated column uses the Points column in its formula (which it has to), so I'm not sure what else I can do??

[parry2k]

@PetyrBaelish you are right, pointforsorting is referring to point column and that's why it is not allow to sort. you can create this "pointforsorting" column in power query instead using dax.

although I'm mot sure it will stil work because you have tied point and try to sort the tied point with non-tied sort column , and it will give error (i think so)

for example , if you have following tied points

Point PointSorted

1       1.01

1       1.02

1       1.03

and if you try to sort point by pointsorted, it will give error message.

I think first and foremost is to create "pointsorted" in power query and go from there.

[PetyrBaelish · Accepted solution]

Thanks for your help. I couldn't do this in Power Query as my Results table contained the following data:

Tournament - Player - Result

22/12/2018 - Danny - 1st

22/12/2018 - Janet - 2nd

I had another table called Points as follows:

Result - Points

1st - 16

2nd - 12

The Points Measure was modelled with the formula:

Points = RELATED('Points'[Points])

I attempted In Power Query Editor to include a custom column with an IF statement (If Results = 1st, 16, If Result = 2nd, 12 ...) but it complained that the term "IF" was unrecognized.

In the end I decided there was no need for a separate Points and PointsForSorting columns, and recreated the Points column as follows:

Points = RELATED('Points'[Points])+(Results[1st]*0.0001)+(Results[2nd]*0.0000001)+(Results[3rd]*0.0000000001)

This seems to have worked.