Hi,
I have a matrix with Years going across columns. I want to sort the current year 2021 by sales value in Descending order. How can i do that. There is no sort option to sort descending by any specific year only by Totals. Is there a hack to sort by values in 2021 descending wise.
@chris786 Refer this:
Thanks Antriksh,
What I’m looking for is not the sort order by columns but I want to sort Year 2021 by values from high to low ( Descending Order) . I do’nt think there is an option to sort individual columns if it is Years…if I click on Year 2021 , it only filters and does not sort. I hope you get what I mean.
Hi @chris786,
Give something like this a go.
Rank Customers by Current Year =
VAR cYear = YEAR( TODAY() )
VAR vTable =
FILTER(
CALCULATETABLE(
SUMMARIZE( Sales, Dates[Year], Customers[Customer Name Index] ),
ALL( Sales )
), [Year] = cYear
)
VAR _Rank = RANKX( vTable, CALCULATE( [Total Sales], Dates[Year] = cYear ), , DESC )
VAR _Result = IF( NOT( ISINSCOPE( Dates[Year] )), _Rank )
RETURN
_Result
In the Matrix, set the sort order to this measure, disable word wrap for Column headers and Values and
manually adjust the column withs to 0 - so they are no longer visible.
I hope this is helpful.
That is Brilliant Melissa,
It worked correctly. I tried to understand your formula, why are we using NOT(ISINSCOPE), isn’t the year showing in the matrix. Could you just put some light on this usage.
Thank you,
Hi @chris786,
I’d say just comment _Result out and return _Rank instead to see what happens…
When minimizing the columns I suppose it doesn’t really matter but it can’t hurt either