You can use something like the following expression:
And no, there isn't a way to enable manual sorting in a pivot table, but there might be workaround in some cases since you can sort a pivot table by an expression and an expression can be a variable that you can allow the user to change by using actions or a macro. This would only work for sorting by expressions values.
Hey thanks for the answers.
It looks good but I can't get it to work in my situation.
Another question regarding MAX that might sort things out:
If I want to use a dimension (say EmployeeID) but I only want to include the newest employees (highest EmployeeID), is there a way to use the max function on my dimension to only include the highest or the 3 highest EmployeeID's?
There is a button in the Dimension window called "Add calculated dimension", is that the right place to add my code and what should i write?
The only thing I've figured out so far is that it should be something like "=MAX([EmployeeID])" but that doesn't seem to work....
Another example to clarify: If I've got two dimensions, EmployeeID and Year and the expression sum(sales), I get Employee 1 2 3 4 in 2006, Employee 1 2 3 4 in 2007 etc. If I only want to get Employee 1 2 3 4 in 2006 (not in the other years) or vice versa; only employee 1 in all the years, how do I add a Max() function to strip year/employeeID to only contain a part of the original values (in this case only 2006 from the Year table that usually contains 2006-2010, or only "1" from the EmployeeID table that usually contains 1-4)?
The construction of a calculated dimension is somewhat different than the construction of an expression. You could create a variable called vMaxEmployeeID like the following which returns the third largest EmployeeID value.
And then in the calculated dimension put something like the following.
You might want to look into bookmarks, too. You can save a bookmark where the selection is an expression like the one below. This way you don't have to battle to fix a QV report.
Notice that you should add the total keyword in this search and also when you are using max(EmployeeID) in a chart with EmployeeID as a dimension.
Try this one:
if(count(total distinct [YourDimension]) - rank(sum([FieldToBeCalculated]))+1<=3,sum([FieldToBeCalculated]),
It will calcule the 3 lowest and highest values.
Then you could use the next expression to colour text:
if(count(total distinct [YourDimension]) - rank(sum([FieldToBeCalculated]))+1<=3,lightred(),
Hope that helps