Forum Discussion
unexpected matrix sort
I am developing a report in Power BI Desktop. My source data comes from an SSAS Tabular Database. The Tabular Database contains the following:
| Region | Country | Visitors |
| Australia | Australia | 100 |
| Asia | China | 150 |
| Asia | India | 50 |
| Asia | Thailand | 20 |
| Asia | Sri Lanka | 10 |
| Africa | Nigeria | 90 |
| Africa | Chad | 70 |
I also have a measure defined in my Tabular Database as follows: avg_visitors:=average(Test[Visitors])
In my report I want to get the above information by avg_visitors and sorted also by avg_visitors using a Matrix. This is the result I would like to see:
| Region | Country | avg_visitors |
| Asia | China | 150 |
| India | 50 | |
| Thailand | 20 | |
| Sri Lanka | 10 | |
| Australlia | Australia | 100 |
| Africa | Nigeria | 90 |
| Chad | 70 |
However, this is what I am actually getting:
So it seems that what is happening is that the sorting is based on the avg_visitors value by Region only. Based on the original table above avg_visitors by Region is 100 for Australia, 80 for Africa and 57.5 for Asia.This explains the sort that Power BI Desktop came up with but I don't understand why that was applied despite specifying the measure within the context of both Region and Country. I can understand this behavior if only Region was specified in the Matrix without including Country.
I guess what I want to get is the sorting based on the Country within each Region such that the Region with the highest Country is first in the matrix, followed by other Countries belonging to that Region, Followed by the next Region with the highest Country and so on...
Would appreciate any feedback/hints. Thanks.
Hi Anonymous,
Please turn off the "Stepped layout". Expand matrix all down one level in hierarchy(highlighted in blue line of second screenshot). You will get similar format as what you want.
But for your order, it's impossible for matrix, because the matrix can only sort by a column. If we sort by average visitors, the Australia is at first, because the average of Australia is 100(maximum), then the average of Africa is 80(so it is second order). In each group, it will order descending, for example, in Africa region, 90->70, in Asia region, 150->50->20->10.
Best Regards,
Angelia
4 Replies
Hi Anonymous,
Please turn off the "Stepped layout". Expand matrix all down one level in hierarchy(highlighted in blue line of second screenshot). You will get similar format as what you want.
But for your order, it's impossible for matrix, because the matrix can only sort by a column. If we sort by average visitors, the Australia is at first, because the average of Australia is 100(maximum), then the average of Africa is 80(so it is second order). In each group, it will order descending, for example, in Africa region, 90->70, in Asia region, 150->50->20->10.
Best Regards,
Angelia