Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi
may be this :
add in your pivot table sorting option under supplier :
sorting by mesure then as mesure
rank(sum({<YEAR={'2020'}> sales))
all mentioned answers were helpful , thanks BR
Hi
may be this :
add in your pivot table sorting option under supplier :
sorting by mesure then as mesure
rank(sum({<YEAR={'2020'}> sales))
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.
all mentioned answers were helpful , thanks BR