locked
Pivot Table Columns disappear from Pivot after creating Set from Column Items RRS feed

  • Question

  • Hi All,

    I have a pivot table with three calculated fields (Qty, No & Ratio). A dimension "Heading" is across the columns (Sales / Stock / Fcst / Net Fcst). A dimension "Product" is on the rows. Relationships exist between the "Heading" and the fact tables containing the data.

    The pivot works perfectly and displays and calculates all values correctly. However, not all "Heading" values require each of the 3 measures (Eg Sales heading requires Qty, No & Ratio but stock only required Qty) and so I created a set from column items to hide the measures that I do not want to see against each Dimension. Immediately i do this the set hides most of the columns. I check the set via manage sets and all the fields are there but they just don't show on the pivot. I remove the set from the pivot and replace it with the original "Heading" and calc fields and it works perfectly. remove it and place the set back and the columns disappear again.

    Hope you can help

    Regards

    Dale

    Thursday, October 16, 2014 7:14 AM

Answers

All replies

  • Dale,

    I am unable to recreate your issue. I created a dataset based on the information in your post and followed your steps - and I seem to be getting the expected behavior.
    See screenshot below and Power Pivot File


    Regards, Avi www.powerpivotpro.com

    Thursday, October 16, 2014 4:12 PM
  • Hi Avi,

    Thanks for the response. The scenario you created is correct but unfortunately didn't replicate my problem. Screenshot of the pivot report and the set below. Initially I only had Qty for stock fields which then didn't show, hence my initial post. I then tried having 2 measures in the set so while you see the No measure it should not show. This did work okay after saving, refreshing, saving as new name, closing, reopening but then after shutting down my machine and reopening this morning it has decided to revert to not showing the columns again. Also attached a screenshot of the data model. The M2M relationships are handled with Gerhard Brueckl's technique of listing all related tables in the calculate statement. This works perfectly and I only have a problem when I create Sets. I can provide a copy of the file if you require but just not able to post it publicly as it is client data that I am working with.

    Regards

    Dale

    

    Wednesday, October 22, 2014 1:49 AM