combining two tables in pivot table

[bertusavius]

Using Power Pivot(which is perhaps not relevant), I am trying to combine two sets of data into one pivot table.
I have created in each table a key column which I use to succesfully relate the tables.

But I cannot get the two corresponding sets properly aligned within the pivot table;

One set gets displayed as totals (as sum of or number of)

Is it not correct to assume that values withing two related tables should be aligned in the pivot table?

 

[powerpivotpro]

Can you tell us a bit more about the contents of each table and what you'd like to see in the pivot? PowerPivot is fantastic at showing data from multiple tables in a single pivot, but I need to know just a bit more about your situation before I can help.

 

[bertusavius]

Ok I understand it is pretty abstract.

I guess the easiest way of explaining it is this:


Table A

___key1___|___|___date___|____string___|___valueA___

Table B

___key2___|___|___date___|____string___|___valueB___

The string is the 'time-bucket' value we discussed earlier (this week).
Both key columns are related in PP.


The goal is to create a Pivot Table like this:

date(column)
__string_|_valueA_|_valueB(nested under date)
__string_|_valueA_|_valueB



I hope it clears things up a little..

 

[bertusavius]

This is better:

Table1:
<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";">Xkey1</td><td style=";">date1</td><td style=";">bucket1</td><td style=";">string1</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">4103050</td><td style="text-align: right;;">1-5-2012</td><td style="text-align: right;;">50</td><td style="text-align: right;;">2</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">4103050</td><td style="text-align: right;;">1-5-2012</td><td style="text-align: right;;">50</td><td style="text-align: right;;">5</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">4103050</td><td style="text-align: right;;">1-5-2012</td><td style="text-align: right;;">50</td><td style="text-align: right;;">9</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">4103050</td><td style="text-align: right;;">1-5-2012</td><td style="text-align: right;;">50</td><td style="text-align: right;;">5</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">4103060</td><td style="text-align: right;;">1-5-2012</td><td style="text-align: right;;">60</td><td style="text-align: right;;">14</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">41030100</td><td style="text-align: right;;">1-5-2012</td><td style="text-align: right;;">100</td><td style="text-align: right;;">15</td></tr></tbody></table>
table 2:
<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";">Xkey2</td><td style=";">date2</td><td style=";">bucket2</td><td style=";">string2</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">4103050</td><td style="text-align: right;;">1-5-2012</td><td style="text-align: right;;">50</td><td style="text-align: right;;">12</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">4103060</td><td style="text-align: right;;">1-5-2012</td><td style="text-align: right;;">60</td><td style="text-align: right;;">15</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">4103070</td><td style="text-align: right;;">1-5-2012</td><td style="text-align: right;;">70</td><td style="text-align: right;;">20</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">4103080</td><td style="text-align: right;;">1-5-2012</td><td style="text-align: right;;">80</td><td style="text-align: right;;">56</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">4103090</td><td style="text-align: right;;">1-5-2012</td><td style="text-align: right;;">90</td><td style="text-align: right;;">41</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">41030100</td><td style="text-align: right;;">1-5-2012</td><td style="text-align: right;;">100</td><td style="text-align: right;;">23</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">4103150</td><td style="text-align: right;;">2-5-2012</td><td style="text-align: right;;">50</td><td style="text-align: right;;">99</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">4103160</td><td style="text-align: right;;">2-5-2012</td><td style="text-align: right;;">60</td><td style="text-align: right;;">98</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">4103170</td><td style="text-align: right;;">2-5-2012</td><td style="text-align: right;;">70</td><td style="text-align: right;;">80</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">4103180</td><td style="text-align: right;;">2-5-2012</td><td style="text-align: right;;">80</td><td style="text-align: right;;">99</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">4103190</td><td style="text-align: right;;">2-5-2012</td><td style="text-align: right;;">90</td><td style="text-align: right;;">102</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">41032100</td><td style="text-align: right;;">3-5-2012</td><td style="text-align: right;;">100</td><td style="text-align: right;;">110</td></tr></tbody></table>
and a correct pivot table:

<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">3</td><td style=";">Rijlabels</td><td style=";">Number of date1</td><td style=";">Som van string2</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">1-5-2012</td><td style="text-align: right;;">6</td><td style="text-align: right;;">167</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">50</td><td style="text-align: right;;">4</td><td style="text-align: right;;">12</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style=";">60</td><td style="text-align: right;;">1</td><td style="text-align: right;;">15</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style=";">70</td><td style="text-align: right;;">
</td><td style="text-align: right;;">20</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style=";">80</td><td style="text-align: right;;">
</td><td style="text-align: right;;">56</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style=";">90</td><td style="text-align: right;;">
</td><td style="text-align: right;;">41</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style=";">100</td><td style="text-align: right;;">1</td><td style="text-align: right;;">23</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style=";">2-5-2012</td><td style="text-align: right;;">
</td><td style="text-align: right;;">478</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style=";">50</td><td style="text-align: right;;">
</td><td style="text-align: right;;">99</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style=";">60</td><td style="text-align: right;;">
</td><td style="text-align: right;;">98</td></tr><tr><td style="color: #161120;text-align: center;">14</td><td style=";">70</td><td style="text-align: right;;">
</td><td style="text-align: right;;">80</td></tr><tr><td style="color: #161120;text-align: center;">15</td><td style=";">80</td><td style="text-align: right;;">
</td><td style="text-align: right;;">99</td></tr><tr><td style="color: #161120;text-align: center;">16</td><td style=";">90</td><td style="text-align: right;;">
</td><td style="text-align: right;;">102</td></tr><tr><td style="color: #161120;text-align: center;">17</td><td style=";">3-5-2012</td><td style="text-align: right;;">
</td><td style="text-align: right;;">110</td></tr><tr><td style="color: #161120;text-align: center;">18</td><td style=";">100</td><td style="text-align: right;;">
</td><td style="text-align: right;;">110</td></tr><tr><td style="color: #161120;text-align: center;">19</td><td style=";">Eindtotaal</td><td style="text-align: right;;">6</td><td style="text-align: right;;">755</td></tr></tbody></table>

Above is a simplyfied version of my actual situation. In this example everything works, but my actual PT looks like this:
Column D only gives totals.

<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">3</td><td style=";">Rijlabels</td><td style=";">Number of Datum</td><td style=";">Som van #</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">27-2-2012</td><td style="text-align: right;;">1062</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">5000600</td><td style="text-align: right;;">1</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style=";">6000700</td><td style="text-align: right;;">12</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style=";">7000800</td><td style="text-align: right;;">56</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style=";">8000900</td><td style="text-align: right;;">56</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style=";">9001000</td><td style="text-align: right;;">78</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style=";">10001100</td><td style="text-align: right;;">63</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style=";">11001200</td><td style="text-align: right;;">58</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style=";">12001300</td><td style="text-align: right;;">85</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style=";">13001400</td><td style="text-align: right;;">115</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">14</td><td style=";">14001500</td><td style="text-align: right;;">59</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">15</td><td style=";">15001600</td><td style="text-align: right;;">108</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">16</td><td style=";">16001700</td><td style="text-align: right;;">150</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">17</td><td style=";">17001800</td><td style="text-align: right;;">52</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">18</td><td style=";">18001900</td><td style="text-align: right;;">39</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">19</td><td style=";">19002000</td><td style="text-align: right;;">33</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">20</td><td style=";">20002100</td><td style="text-align: right;;">29</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">21</td><td style=";">21002200</td><td style="text-align: right;;">45</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">22</td><td style=";">22002300</td><td style="text-align: right;;">16</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">23</td><td style=";">23002400</td><td style="text-align: right;;">3</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">24</td><td style=";">24002500</td><td style="text-align: right;;">3</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">25</td><td style=";">25002600</td><td style="text-align: right;;">1</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">26</td><td style=";">28-2-2012</td><td style="text-align: right;;">1415</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">27</td><td style=";">4000500</td><td style="text-align: right;;">1</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">28</td><td style=";">5000600</td><td style="text-align: right;;">1</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">29</td><td style=";">6000700</td><td style="text-align: right;;">11</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">30</td><td style=";">7000800</td><td style="text-align: right;;">57</td><td style="text-align: right;;">8544</td></tr><tr><td style="color: #161120;text-align: center;">31</td><td style=";">8000900</td><td style="text-align: right;;">64</td><td style="text-align: right;;">8544</td></tr></tbody></table>

Blad5​

No matter how I twist or turn the PT; I can't get it right.
What could be the problem?

 

[powerpivotpro]

You need two more tables. Both can be single-column tables.

One is just the list of Buckets, the other is the list of all Dates.

Then you create four relationships (relate each of those two new "lookup" tables to each of your original tables, and make sure you use the new tables as the Lookup tables in the relationship dialog).

Then the fields you put on Rows of the pivot MUST be from the two new lookup tables.

More details in these posts:

http://www.powerpivotpro.com/2012/01/salesbudget-integrating-data-of-different-grains/

http://www.powerpivotpro.com/2012/01/data-of-different-grains-a-followup/

 

[bertusavius]

Ecspecially the second link provided me with a close copy of my specific situation.

Your solutions worked like a charm.

Many thanks again.

 

[powerpivotpro]

Awesome, feels good to hear it

 

[pbrayudu]

Awesome, feels good to hear it

Sir,

This is a great tip. Today, i have searched for solution and found this tip and worked well.

I had purchased e version of "DAX formulas for Powerpivot " from Mr. Excel store and "Powerpivot Alchemy" (Book) from M/s Amazon.

Thanking you,

Best regards

P. Bangaru Rayudu
Reliability Cost Engineer
Abu Dhabi, UAE.