How to sort the pivot table?

[#1]

Hi All,

I have a pivot table.

but I am not able to Sort in that pivot table.

My Pivot table like below.

And sort tab of my pivot table like below

I have like this but Sorting is not worked in my pivot table.

Is it possible to sort in the pivot table?

I have three dimension , that's why I have a problem?

please help me.

Thanks in advance....

Regards,

Kabilan K.

[#2]

The Goal:

Sort expression columns in a pivot table

The Method:

1. Create variables for the expressions you want to sort by

LET vEqProjectBaseline = 'Sum([BackingSheetHours]) /7.5';
LET vEqEstimate = 'Sum(Aggr([Estimated Hours], ProjectTaskKey)) /7.5';
LET vEqBaselineVsEstimate = '($(vEqEstimate)) / ($(vEqProjectBaseline))';

     N.B – brackets and apostrophes are a real minefield

2. Create function to evaluate the variables inline

SET fxEval = Evaluate($1);

3. Create the custom sort table:

CustomSorting:
LOAD %DisplayValue
,%ExVariable
,$(fxEval(%ExVariable)) as %SortingExpression
Inline
[
%DisplayValue ,%ExVariable
Project Baseline ,vEqProjectBaseline
Estimate ,vEqEstimate
Baseline vs Estimate      ,vEqBaselineVsEstimate

];

4. Set the sort expression in the pivot table:

5. Create a selector for the sorting options (I used %DisplayValue in this example)

The Goods:

Adds sorting to pivot tables

Doesn’t seem to affect performance (not yet tested on large datasets)

The Bads:

This will probably be a bummer to maintain (– I’m thinking mismatching expressions between the table and the variables)

Takes a couple of hours to set up and test

You'll only be able to sort ASC [OR] DESC, not have the option of both

Hope that helps!

Rich