unexpected matrix sort

[Anonymous]

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.

[v-huizhn-msft · Accepted solution]

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

[Anonymous]

The 2nd table in my original post was is not showing propertly I am adding it here as an image:

[v-huizhn-msft · Accepted solution]

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

[Anonymous · Accepted solution]

Thanks for explaining it.

[v-huizhn-msft]

Hi @Anonymous,

You're welcome. Could you please mark the reply as anser if it's useful? So we can close the thread and more peole will get helpful information clearly and easily.

Thanks,
Angelia