Forum Discussion
custom sorting in a matrix table
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]).
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.
It looks like the bookmarks are gonna work fine.
However I see my ranking function needs some modification.
In the first example it worked fine because the sales values were what they were. If I modify them (look below) the sorting is not good.
I need to rank Sales taking into account just total sales of Products, and the rest (mon
I want to add Year as well
Of course it is just a sample. The real table has thousand of rows, tens of measures, etc.
I suppose something should be done to RANKX function, for example not ALLSELECT but maybe ALLEXCEPT or mabe some FILTER inside. I tried to google it but without success yet. I am a beginner.
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]).