Sort by value

[barlevitzky]

Hi all,

I am trying to sort these table columns by values (column revenue), so the higher total value will be the first from the left and so on. 

I clicked on the sort by column but nothing happened. 

Thanks

[barlevitzky · Accepted solution]

Hi All,

I found a workaround solution. 

After I changed between Rows and Columns parameter (ID will be Rows instead of Date) and apply column subtotal, I was able to sort by Sum of revenue values.

So the final result wasn't exactly like I wanted to but we will have to adjust it. 

[wwhittenton]

Hi  @barlevitzky,

I'm not sure the Matrix visual can do what you're asking, but give this a shot:

In your matrix, click on the elipses at the top right. In the dialogue box that appears, see if there is a "sort by 'x'" section that will do what you're looking for.

[barlevitzky]

Hi @wwhittenton

Done that, nothing changed.

[wwhittenton]

@barlevitzky

Alright, I think I may have it. You'll want to create a new table that can handle the totaling for you, by doing the following:

1. create a new table that houses the values you're looking to use:

Table2 = SUMMARIZE( 'table' , 'table'[ID] )

2. calculate the totals in a new column that show up at the bottom of the Matrix:

Sum of Revenue= CALCULATE ( SUM( 'table'[revenue] ) , FILTER( 'table' , 'Table2'[ID] = 'table'[ID] ))

3. Create a rank column of these values:

Rank = RANKX( ALL( 'Table2' , 'Table2'[Sum of Revenue] , , DESC )

Then connect the 'Table2'[ID] column to your original data (the "one" side will be the new table), replace the columns in the Matrix visual with the new 'Table2'[ID], and Sort by Column on the Rank you just created.

EDIT: I realized that the Sort by Column feature won't let you sort based on the Rank, due to the FILTER being applied to get it. So as a workaround, you could concatenate the Rank and ID columns and use it as the column heading in your Matrix:

RankID = 'Table2'[Rank] & ". " & 'Table2'[ID]

[wwhittenton]

@barlevitzky Are the columns being created from a single data source? i.e. 407, 537, 552, etc. are all loaded into the matrix through one data source?

[Abduvali]

@wwhittenton

I got his point:

• He is using Sales Qty in Columns field in the actual Matrix and would like to see Qty's in a Desc order not sure if it's actually possible.

[barlevitzky]

@Abduvali, yes. these numbers are coming from "ID" column. 

we can relate that to store id and revenue per store. So i want to see first the store with the highest revenue. 

[Abduvali]

Hi @barlevitzky,

Use sorting on the column in the actual table:

Regards

Abduvali

[barlevitzky]

Hi, @Abduvali

I am using Matrix and not a Table.

Sorry that I wasn't clear about it. 

[Abduvali]

My screenshot is on Matrix as well.

[barlevitzky]

@Abduvali

1. The total in your sales column is higher, so sales column should be the left column. 

2. I don't know what is the difference between our matrixes, but when I point with the mouse on the column title I don't have sort option. only on the date column i have it. 

[Abduvali]

@barlevitzky

You can download this file from my library:

Here is the link Power BI User Group Dublin:

Regards

Abduvali