Is there a way to have a Table Hierarchy? Or Multiple Header Tables?

[Mark Bit]

Anyone know if it is possible, and if so, how to have multiple headers in a table? I know it sounds counterintuitive but I would like a way to pivot a bulk set of data (set in an Excel Data Table) but have the option to choose 1 of 2 Options when choosing my data header for the Pivot.

Let me try and explain a scenario. So each row has an Account, Part #, a specific week ending date units sold, and a specific week ending date unit dollars. For each account, we sell maybe 5-7 products. So Retailer A has 5 unique rows, Retailer B has 7 unique rows, yadda yadda.

I have 4 tabs, very similar of one another, the only variable is the year (specified by the tab) – or – table name … and the removal and inclusion of obsolete and new products.

Our Sales weeks are Sunday-Saturday. So, I have 2 column headers that read 11/15 Units and 11/15 Dollars; for this past week.

However, If I wanted to compare this past week to last year or 2012, using Power Pivots, I would not be able to – since “week endings” do not lineup. That is to say in 2013 Week 46 (this past week-end week) correlates to 11/16.

So I would not be able to do a 1:1 comparison easily within a Pivot due to the variation in dates. So logically, I would need some sort of common denominator. I would imagine this would be Week “X.” So essentially two separate (or hierarchy?) column headers. I would either need “Week 46″ act as a parent – above both “11/15 Units” and “11/15 Dollars” … or alternatively two separate “co-headers” right above them: “Week 46 Units” and “Week 46 Dollars”

This way, I could compare multiple Week Ending sales data, across multiple tables, using a Power Pivots, based on a common property.

Does anything like this exist? I feel like I would have come across it by now if it does.

And to note, I cannot simply just change the way we handle data to reflect a Week # and omit the XX/XX date methodology. I wish I could, cause that would be abundantly easier.


....Example Included.

 

[Luke M]

Hi Mark,

As your last paragraph seems to indicate, good data table setup is key. But, sometimes we have to work with what we're given.

I don't believe we can do hierachy's like you show w/o basically changing the complete layout (in which case, we would just switch naming).

So, my proposal would be to use INDEX. Since the week 1 units are always the 5th column, we could refer to it via:
=INDEX(RRR_201535[#Data],,5)

Obviously, the last argument would be supplied via another function or a cell reference. So, I can imagine having a cell where you fill in a week number (say 2 in cell A2). A quick formula of:
=4+(a2-1)*2+1
will calculate the correct column number within table for Units. Formula for dollars would be:
=5+(a2-1)*2+1

Does that give you some ideas?

 

[Mark Bit]

I would have to see an example I suppose, but I at least know I need to do some work-around haha. It just would be nice to easily compare Week 1 to Week 1 of any given year - via a Power Pivot. Ideally I would bypass the date structure - and simply use Week 1, Week 2, etc - for universal purposes. However, the data entry is somewhat contingent on the dates.

 

[Luke M]

Here's an example...you can change the value in A1 to be a number indicating which week you want, and formulas pull values for that week.

 

[Mark Bit]

Hmmm interesting, I think I certainly could run with this. Multi week will take some thought.

Thanks again for all your help. Much appreciated!