locked
Creating Hierarchies RRS feed

  • Question

  • Hi there,

    I am trying to create an alternate hierarchy on the fly in PowerPivot, but my data is from a single cube/table and the "Relations" icon is shaded out, so I can't look at the DIMs that way.  I also tried right-clicking at the top of the column (for my DIM), but I don't see the option to create a hierarchy - actually, I don't see that option anywhere.

    Is it the case that I cannot create an alternate hierarchy from an existing DIM when I only have one table?  If this is not the case, how would I do it?

    Thanks...........

    Tuesday, June 9, 2015 8:16 PM

Answers

All replies

  • Hi LT-LU48,

    According to your description, you need to create alternate hierarchy in your PowerPivot data model which on have one table, right?

    A hierarchy is a list of columns that are considered a single item when used in a Pivot or Power View report. It easier for users to select and navigate common paths of data when creating reports and pivot table. To create hierarchies, use the Power Pivot add-in. Please refer to the links below to see the detail steps to create hierarchy.
    https://msdn.microsoft.com/en-us/library/hh272056%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396
    https://support.office.com/en-nz/article/Hierarchies-in-Power-Pivot-002cf883-3b5f-497c-bfa1-ab2271cdb73b

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Michael Amadi Thursday, June 18, 2015 11:12 PM
    • Marked as answer by Charlie Liao Wednesday, June 24, 2015 2:12 AM
    Thursday, June 11, 2015 2:43 AM
  • Hi LT-LU48,

    Did the link provided by Charlie help you to resolve your issue? If not can you share a screenshot (with sensitive details removed) of where you are trying to create the alternate hierarchy so that we can get a better idea of your scenario.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Thursday, June 18, 2015 11:12 PM
  • Hi there,

    Sorry for not getting back sooner, actually, no, the link did not help as I am on Excel 2010 and wouldn't even see the diagram icon in the ribbon (I believe this is the case from other boards I've read). 

    My data is coming in as Charlie was saying, each column is a level in my hierarchy - so, column 1 is the highest level and column 2 is the next level down and so on.  What I would like to do is to pull data from the lowest level for both account (i.e. g/l) and company hierarchy, as well as by month (again the lowest level in my time DIM) and set up the Power Pivot to see the data at any level, without going back to the cube directly for performance purposes.

    So, what I have to do is to have the columns become the tree so that we can "drill down", but more importantly so that the values roll up properly, because right now it is actually summing up all the levels with the detail.  In other words all the values are double/multiple counting for each level.

    I tried to include the screen shot, but I get an error message about the size of my post.....sorry....

    Thanks for your help.......

    Thursday, July 2, 2015 4:46 PM
  • I am also not sure what he meant by "To create hierarchies, use the Power Pivot add-in", is there a specific hierarchies add in for Power Pivot that I need to install?  Or is Charlie referring to Power Pivot in general, because I already have that - but the Excel 2010 version (I think it's the 2nd version).

    Thanks again....

    Thursday, July 2, 2015 4:49 PM