Power BI Blog: All in Order

Welcome back to this week’s edition of the Power BI blog series. This week, we look at a problem when sorting a column by another column.

We have two Table visualisations that we have created in Power BI Desktop. In each case we want to change the order of the rows from the default ordering. The first Table visualisation shows the sales targets for each month, which as you might expect for a tent hire business, is seasonal:

This demonstrates a common issue: we have the months in order, but they are in alphabetical order, not chronological order. From the Table visualisation, we only have the option to sort on Month or Sales Target:

To sort this out, we select the Month column and go to the ‘Column Tools’ tab:

There is an option to ‘Sort by Column’. The Sales Targets table has a Month Number, so we can choose to sort Month by Month Number.

We click on Month Number, and the Table visualisation now displays Month in chronological order:

The next example shows our imaginary salespeople and the date they joined.

The data has automatically been sorted into Join Date order.

There is another column which is not shown on the Table visualisation:

The management would like the data to be sorted by Awards. However, if we follow the same process as before and try to sort Join Date by Awards, we get an error:

The reason for this is that two of the salespeople have the same Join Date but different Awards.

This error is telling us that it doesn’t know how to sort the two dates Monday, 15 February 1999 according to the different Awards for each of these records.

We can’t use Join Date as the driver in this case as, even if we could use a different column to sort by, we need to show Mary above Derek in the Table visualisation as she has more Awards than Derek. Instead, we will use Name as the column which we will sort by Awards.

This does sort the data by Awards, viz.

However, we can only sort in ascending order! There is no option to pick ascending or descending in ‘Sort by Column’. Since we can’t change the order, we must change the column we sort by.

In the Data tab, we create a new column:

We want this column to reverse the order of Awards, so we can define it as:

where the one [1] is added so we never try to divide by zero [0].

Now we can sort Name by Inverse Awards:

However, management has decided that we must sort by Join Date within Awards (so we should show Derek before Claire).

The easiest way to solve this for this example is to go to the Power Query editor via the ‘Transform Data’ option on the Home tab.

We can then sort the data into the required order by sorting first on Awards, in descending order:

We then sort on Join Date in ascending order to get our data in the order we want to see on the Table visualisation.

Now we can add an index column from the ‘Add Column’ tab:

We call our new column Record Count.

We can now ‘Close & Apply’ from the Home tab, and sort Name by the new column Record Count.

Our data is now sorted correctly.

Check back next week for more Power BI tips and tricks!