locked
How to Sort Dimension in Pivot Table via Order Column which is changing like Factual values RRS feed

  • Question

  • Hi,

    Recently in of our product offerings we got stuck on this following question:

    How to Sort Dimension based on the Order Value which Keeps Changing with Factual Values??

    We have a data source laid out as (example)

    In the above the “Order” columns are changing per Company/(DimensionA) for DimesnsionB.


    Instead what we want is: (But only if we can get the following result without putting the “Order” Column in the “Values” Section. 


    If there are any configurations that we can make to our power pivot model for the similar data set so that the DimesnionB in this case can be sorted by the Order column, would be greatly helpful to us. 

    Sample File:

    http://tms.managility.com.au/query_example.xlsx

    Thanks

    Amol 

    Friday, March 27, 2015 12:34 AM

Answers

All replies

  • Hi Amol,

    You can sort a colum by a value from a different column within the same table:

    In the Home-tab of your PP window - sort&filter-section - sort by column, click the little triangle

    But this requires unique entries in the columns to sort and to sort by.

    In your case, you'd need to create 2 disctinct fields: CompanyA&CompanyB (to sort) and CompanyA&Order (to sort by)


    Imke


    Friday, March 27, 2015 7:02 AM
    Answerer
  • Hi Amol,

    According to your description, you need to sort dimension members in Pivot Table via order column, and you don't want the order column show on the Pivot table, right?

    Based on my research, we can sort the data on the Pivot table based on one of the columns in that table, and we cannot sort the data based on the columns that not existed on the Pivot table. So in your scenario, to achieve your requirement, you can add the column to pivot table and hide it.

    https://support.office.com/en-gb/article/Sort-data-in-a-PivotTable-or-a-PivotChart-report-49efc50a-c8d9-4d34-a254-632794ff1e6e

    Regards,


    Charlie Liao
    TechNet Community Support


    Friday, March 27, 2015 8:48 AM