locked
Grouping row labels power pivot chart RRS feed

  • General discussion

  • Hello MSDN Forum,

    Unfortuantly I cannot add pictures in this post, since I am a new user.

    I have the following example data model goo.gl/29xGm8

    The real data model contains a large number of columns in treeFamilyData. My goal is to create a power pivot table that looks like the table in the sheet: wanted table.

    So I only want the group a few of the row labels under the Field labels and not all of the row labels. I accomplished the above result by putting two pivot tables below each other and hiding the header rows of the lower pivot table. The disadvantage of the method is that due to sorting in the horizontal tree order, the upper and lower pivot table not matches.  Is it possible to accomplish this format in a single pivot table? If I need to use two pivot tables is there a way to force the horizontal tree order in de lower table to match to tree order in de upper table?

    Best Regards,

    user38191

    Wednesday, May 31, 2017 1:26 PM

All replies

  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Transform data with PQ into a PP friendly format.
    Doable with DAX, but harder.
    http://www.mediafire.com/file/yaaxgbpe5nq8mdq/05_31_17.xlsx
    http://www.mediafire.com/file/jb1qe9n081eh8w1/05_31_17.pdf

    Thursday, June 1, 2017 12:03 AM
  • Hi user38191,

    >>So I only want the group a few of the row labels under the Field labels and not all of the row labels.

    I am confused what you said. For one row level, you can filter and select the value to display or not.



    You can share your sample table to onedrive and post the sharing link. Without sample data, it's hard to reproduce your requirement.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 1, 2017 2:53 AM
  • I have the following example data model:

    The real data model contains a large number of columns in treeFamilyData. The example data model can be found here: link. My goal is to create a power pivot table that looks like this:


    So I only want the group a few of the row labels under the Field labels and not all of the row labels. I accomplished the above result by putting two pivot tables below each other and hiding the header rows of the lower pivot table. The disadvantage of the method is that due to sorting in the horizontal tree order, the upper and lower pivot table not matches.  Is it possible to accomplish this format in a single pivot table? If I need to use two pivot tables is there a way to force the horizontal tree order in de lower table to match to tree order in de upper table?

    Thursday, June 1, 2017 7:10 AM
  • Hi @user38191,

    As I tested, it is impossible to accomplish this format in a single pivot table. If you use two pivot tables, you can order the column header name using same order in lower and upper tables. Please see the highlighted in yellow background.



    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, June 1, 2017 8:30 AM
  • Hello Angelia,

    I know how to sort the trees. But I have a very large number of trees and I do not sort them in alphabetical name order but in order of number of trees. The second pivot table does not contain the value number of trees so I cannot sort the second pivot table. I can add this value to the second pivot table and use it for sorting, but I do not want the number of trees visible in the second table. I then tried to hide the number of trees in the second table but then also the field and branch height get hidden. Any suggestions how to sort both tables on: Sum of number of trees?


    Thursday, June 1, 2017 12:09 PM
  • User, any progress on this?

    Thanks!


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Monday, October 9, 2017 8:48 PM
  • Please kindly mark the correct solutions as answers, this could be beneficial to other community members who are facing similar issue. Your contribution is highly appreciated.

    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Thursday, January 18, 2018 11:43 AM
    Answerer