Sorting by measure in a pivot table but only one special year

[MichaelFL]

Hello ,

i would like to sort by a measure in a Pivot table.

In my example, you can see two dimension. Supplier and Year. The year has been generated with year(date).

How to sort only by year 2020 top down?

[brunobertels · Accepted solution]

Hi

may be this :

add in your pivot table sorting option under supplier : 

sorting by mesure then as mesure 

rank(sum({<YEAR={'2020'}> sales))

[GaryGiles]

Not sure what aggregation you are using in your measure. 

One option is to calculate the Year in the load script so you have a field, like InvoiceYear

Year(date) as InvoiceYear

Then, in your pivot sort, turn off Auto sort for Supplier and check Sort by Expression.  In the expression, try:

=sum({$<InvoiceYear={2020}>} Value)

Note: if you want to give the user the option to change the sort year, you can use a variable.

You calculate the year in the load script because you can't use a function in the left part of an equal symbol in set analysis.  If you are not able to change the load script, you could use the following in the sort expression:

=sum({$<date={">=01/01/2020<=12/31/2020"}>} Value)

Again, you could setup the year as a variable if you wanted the user to be able to change.

You can change the Sort direction by choosing Ascending or Descending.

Hope this helps.

[MichaelFL · Accepted solution]

all mentioned answers were helpful , thanks   BR