cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
J_K Regular Visitor
Regular Visitor

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:

 

RegionCountryVisitors
AustraliaAustralia100
AsiaChina150
AsiaIndia50
AsiaThailand20
AsiaSri Lanka10
AfricaNigeria90
AfricaChad70

 

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:

 

RegionCountryavg_visitors
AsiaChina150
India50
Thailand20
Sri Lanka10
AustralliaAustralia100
AfricaNigeria90
Chad70

 

However, this is what I am actually getting:

 

Capture.JPG

 

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.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-huizhn-msft Super Contributor
Super Contributor

Re: unexpected matrix sort

Hi @J_K,

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.

2.PNG

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.

1.PNG

Best Regards,
Angelia

View solution in original post

J_K Regular Visitor
Regular Visitor

Re: unexpected matrix sort

Thanks for explaining it.

View solution in original post

4 REPLIES 4
J_K Regular Visitor
Regular Visitor

Re: unexpected matrix sort

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

 

Capture2.JPG

 

 

 

v-huizhn-msft Super Contributor
Super Contributor

Re: unexpected matrix sort

Hi @J_K,

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.

2.PNG

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.

1.PNG

Best Regards,
Angelia

View solution in original post

J_K Regular Visitor
Regular Visitor

Re: unexpected matrix sort

Thanks for explaining it.

View solution in original post

v-huizhn-msft Super Contributor
Super Contributor

Re: unexpected matrix sort

Hi @J_K,

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 249 members 2,329 guests
Please welcome our newest community members: