Filtering or Sorting on Value's in Pivot

Question

Hi All,

Something im finding quite limiting with Powerpivot is the inability to filter or sort on Value's. I have no idea why this was introduced but it nearly makes certain pivots pointless.

Any work around short of copying the data into another sheet and using it from there which again makes it all a bit a pointless.

 

Cheers,

Fionn

Answer 1

Hi Fionny,

Do you have an example what you want to do ? A lot of filtering and sorting options are available but I would like to know you situation.

Kasper

Answer 2

 

Below is a sample of the Pivot, what i wanted to do was filter and sort on the "Sum of Sales Amount" Column. Autofilter was restricted to the row labels only and right clicking on the value's and select sort Largest to Smallest would only apply the sort to the Exchange Rate Column...

 

Powerpivot is proving itself quite buggy and unfinished really, which is unfortunate.... they still havent sorted the Dates Grouping either which is a HUGE hole. 

 

 

No_

Name Currency Code Exchange Rate Sum of Sum of Sales Amount  Excl VAT Sum of Trade Discount Sum of Settlement Sum of Carriage Sum of Assembly Sum of Rebate VX00006 Chairs STG 0.85 451216 0 5 0 0 0 VX00014 Beds   1 5132121 12.5 2.5 0 0 2 VX00010 Lamps STG 0.85 0 0 4.76 2 2.5 3

 

EDIT:

 

Sorry the excel formatting gets lost even though its pastes in fine.

 

Answer 3

Hi Fionny,

 

The sorting in PivotTable is supported on VALUES columns and it works fine for every column for me. Can you specify what happens on your workbook when you try to sort from largest to smallest on any of the VALUES columns?

 

Filtering on Values is not really supported. Could you specify your scenario that requires filtering based on the VALUES? You may want to refine your Measure formulas with DAX to apply filtering.

 

Thanks,

Vaghinak

Answer 4

Hi Vaghinak,

It seems absoluley stupid that you cannot filter on Value's in the pivot, why would this option be disabled, I am aware i can do the same thing by editing my power pivot query but that is a lot of work for a simple task, ie if I was looking at sales margins and wanted to just see sales below 30% margin, then after maybe see margins over 60%.... these are things business like to do alot and to have to change queries all the time would be a real pain.

 

As for the sort if I right click say on my Sum of Sales Amount Value's it is instead sorting the Last label column in my case Exchange Rate....

 

Thanks for your help, but i think this tool is rapidly proving itself too much work for not much benefit.