Reorder factored matrix columns in Power BI

Question

I have a matrix visual in Power BI. The columns are departments and the rows years. The values are counts of people in each department each year. The departments obviously don't have a natural ordering, BUT I would like to reorder them using the total column count for each department in descending order.

For example, if Department C has 100 people total over the years (rows), and all the other departments have fewer, I want Department C to come first.

I have seen other solutions that add an index column, but this doesn't work very well for me because the "count of people" variable is what I want to index by and that doesn't already exist in my data. Rather it's a calculation based on individual people which each have a department and year.

If anyone can point me to an easy way of changing the column ordering/sorting that would be splendid!

      | DeptA | DeptB | DeptC
------|-------|-------|-------
1900  |   2   |   5   |  10
2000  |   6   |   7   |   2
2010  |  10   |   1   |  12
2020  |   0   |   3   |  30
------|-------|-------|-------
Total |  18   |  16   |  54

Order:   #2      #3      #1

Alexis Olson Alexis Olson · Accepted Answer · 2018-10-11T01:29:04

I don't think there is a built-in way to do this like there is for sorting the rows (there should be though, so go vote for a similar idea here), but here's a possible workaround.

I will assume your source table is called Employees and looks something like this:

Department  Year   Value
A           1900    2
B           1900    5
C           1900   10
A           2000    6
B           2000    7
C           2000    2
A           2010   10
B           2010    1
C           2010   12
A           2020    0
B           2020    3
C           2020   30

First, create a new calculated table like this:

Depts = SUMMARIZE(Employees, Employees[Department], "Total", SUM(Employees[Value]))

This should give you a short table as follows:

Department  Total
A           18
B           16
C           54

From this, you can easily rank the totals with a calculated column on this Depts table:

Rank = RANKX('Depts', 'Depts'[Total])

Make sure your new Depts table is related to the original Employees table on the Department column.

Under the Data tab, use Modeling > Sort by Column to sort Depts[Department] by Depts[Rank].

Finally, replace the Employees[Department] with Depts[Department] on your matrix visual and you should get the following:

Reorder factored matrix columns in Power BI

1 Answers