Forum Discussion
custom sorting in a matrix table
Hi,
I have the following table:
| Product | Month | Sales | Volume |
| A | 1 | 50 | 40 |
| A | 2 | 60 | 30 |
| A | 3 | 40 | 20 |
| B | 1 | 40 | 30 |
| B | 2 | 30 | 20 |
which I would like to show in matrix with a sorting by Sales (ascending or descending) but always keeping the months chronological (ascending).
By default the sorting is made on each rows so the months are messed up:
After watching https://www.youtube.com/watch?v=pM_8zsPidRk on RANKX function I created a measure:
Months are ok, but it is not what I want. It misses all the user friendliness.
I would like to sort the Sales by pressing these little triangles but in a way that the months are always shown ascending.
So it should look like:
Is there a way to achieve this?
If not by triangles maybe there is any other sollution? For example by making the "ranking" column invisible and by adding in some sorting buttons which would change the "ranking" column method?
Hi bantoszewski
You can use calculate on your measure to remove the Month filter to rank the productsRank measure =RANKX(ALLSELECTED('Test Table'[Product]),CALCULATE([SalesAm],ALLSELECTED('Test Table'[Month])),,DESC,Skip)If the year/month are from a seperate calendar table you could do AllSelected(CalendarTable) or if they are from the sale Date column use AllSelected(table[date]). If the same table different columns AllSelected(table[month],table[year]).
6 Replies
Hi bantoszewski
Have you tried using bookmarks?
You can save the visual how you want it sorted.
Then save it sorted the opposite direction.
Then assign both of these to images/shapes etc such as a little arrow on the visual.
You could also turn WordWrap off and shrink the rank column down to hide it.
I thought about bookmarks but then what about using slicers, for example on chosen products or on certain dates? As far as I'm concerned the bookmarks remebers all filters.
Hi bantoszewski
Bookmarks can save the settings of an entire page or single/groups of visuals:
When you save the bookmark only select the table and NOT the slicers.
That way it only save the table not the visuals.
Thank you very much. It works 🙂
