Sort Current Year in Matrix by values in Descending order

Power BI
1

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.

Sort

2

@chris786 Refer this:

2 Likes
3

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.

4

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.

image
image1349×571 64.3 KB

I hope this is helpful.

4 Likes
5

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,

6

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 :wink: