Sorting of sales in visual (matrix table) by specific year

[AnnaKi]

Hi!

I have a table matrix visual with Sales per customer and Year. My Rows are customer, Columns Year and Values Sales.

 I want the customers to be sorted by Sales, this is no problem, the problem is that I want to sort customers by ONLY the sales in the specific  year of 2021. How can this be achieved?

Thanks,

[v-kelly-msft]

Hi  @AnnaKi ,

Create 2 columns in your table:

rank = if('Table'[Year]=2021,RANKX(FILTER('Table','Table'[Year]=2021),'Table'[Value],,DESC,Dense))

Column = IF('Table'[rank]=BLANK(),CALCULATE(MAX('Table'[rank]),FILTER('Table','Table'[Customer]=EARLIER('Table'[Customer]))),'Table'[rank])

Then copy customer column and make it sort by Column,and you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[AnnaKi]

Thanks, at the moment my measure can't find the field'Table'[Year] at the beginning of the IF(-statement If('Table'[Year] for some reason (In my model it is called 'DimDate'[Year]). But if I get the model to find it maybe it will work....

[v-kelly-msft]

Hi @AnnaKi ，

Is your issue solved?

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[v-kelly-msft]

Hi  @AnnaKi ,

Measure cant do it,I was using calculated columns,and it needs to be done in the fact table.

Check my .pbix file for details.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[AnnaKi]

Hi!

Thank you so much for your help. I can't get it to work though.

[Year] is in my 'DimDate' table (Calendar) and [Revenue] is in my fact table 'Transactions'.

If I add it in my 'Transactions' (fact) table I get this:

If I add a rank column in 'DimDate' table I get this:

Thanks again 🙂

[TomMartens]

Hey @AnnaKi ,

this article explains how you can sort a table visual using multiple columns, of course you can use a simple on the column header to sort by the last year: https://www.c-sharpcorner.com/article/how-to-sort-a-table-using-multiple-columns-in-power-bi/

Hopefully, this provides what you are looking for.

Regards,

Tom

[AnnaKi]

Sorry! I forgot to say it was a matrix table so I could't do that.