locked
PowerPivot PivotTable independent column RRS feed

  • Question

  • Hi all,

    I want to present a Pivot table with two Fact data, but i need one of them by each Column Label, 
    BUT the other one only in Grand Total.

    This is what actually get (using AdventureWorks for sample purpose)

    actual wrong

    This is what I want (I got it by hiding columns but i lost dynamism)

    wanted

    Greetings and thanks in advance
    Tito Luyo Murata

    Friday, March 9, 2012 8:48 PM

Answers

  • Hi Tito,

    You can do this by creating a set in Excel.   Go to the 'Pivot Table Tools' tab on the ribbon, and under 'Options' go to 'Fields, Items & Sets'.  Then select 'Create a set based on Column Items'. From here, you will be able to select which columns to actually show in the pivot table.




    Javier Guillen
    http://javierguillen.wordpress.com/

    • Marked as answer by Challen Fu Monday, March 19, 2012 9:41 AM
    Sunday, March 11, 2012 1:58 AM
    Answerer
  • Hi Tito,

    Do you just want to keep one Total Sum of OrderQty? Besides Javier's advise, I think you can also set font's color to white. Before this you should exchange the sequence of these two measures.

    In additon, you can also clear the checkbox of "Show Grand Totals for Rows" in Totals&Filters tab in PivotTable Options dialogbox. Then add a customize Total column by typing the expression, this expression should be only the cellbox of OrderQty measure.

    Thanks,


    Challen Fu

    TechNet Community Support

    • Marked as answer by Challen Fu Monday, March 19, 2012 9:41 AM
    Monday, March 12, 2012 2:09 AM

All replies

  • Hi Tito,

    You can do this by creating a set in Excel.   Go to the 'Pivot Table Tools' tab on the ribbon, and under 'Options' go to 'Fields, Items & Sets'.  Then select 'Create a set based on Column Items'. From here, you will be able to select which columns to actually show in the pivot table.




    Javier Guillen
    http://javierguillen.wordpress.com/

    • Marked as answer by Challen Fu Monday, March 19, 2012 9:41 AM
    Sunday, March 11, 2012 1:58 AM
    Answerer
  • Hi Tito,

    Do you just want to keep one Total Sum of OrderQty? Besides Javier's advise, I think you can also set font's color to white. Before this you should exchange the sequence of these two measures.

    In additon, you can also clear the checkbox of "Show Grand Totals for Rows" in Totals&Filters tab in PivotTable Options dialogbox. Then add a customize Total column by typing the expression, this expression should be only the cellbox of OrderQty measure.

    Thanks,


    Challen Fu

    TechNet Community Support

    • Marked as answer by Challen Fu Monday, March 19, 2012 9:41 AM
    Monday, March 12, 2012 2:09 AM