Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
barlevitzky
Helper II
Helper II

Sort by value

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. 

Capture.JPG

 

Thanks

1 ACCEPTED SOLUTION
barlevitzky
Helper II
Helper II

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. 

 

Capture.JPG

View solution in original post

12 REPLIES 12
barlevitzky
Helper II
Helper II

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. 

 

Capture.JPG

wwhittenton
Helper II
Helper II

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.

 

elipses sort in matrix.PNG

Hi @wwhittenton

 

Done that, nothing changed.

Capture1.JPG

@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]

@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?

@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.

 

@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
Skilled Sharer
Skilled Sharer

Hi @barlevitzky,

 

Use sorting on the column in the actual table:

Capture29.PNG

 

 

Regards

Abduvali

 

 

Hi, @Abduvali

I am using Matrix and not a Table.

Sorry that I wasn't clear about it. 

My screenshot is on Matrix as well.

@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. 

@barlevitzky

 

You can download this file from my library:

Here is the link Power BI User Group Dublin:

 

Regards

Abduvali

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.