Use of powerpivot for comparing unknown related periods

[Topdown]

Hi all,
I am experienced in Excel, Databases and but just getting to grips with Powerpivot.

Challenge:

I have a table in SQL Server with records pertaining to a period (column 1). This table is populated with the same keys (column 2) for other periods also.
Example:
<table class="MsoNormalTable" style="width:235.0pt;margin-left:4.7pt;border-collapse:collapse;mso-yfti-tbllook: 1184;mso-padding-alt:0cm 5.4pt 0cm 5.4pt" width="313" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;height:15.0pt"> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap"> Period
</td> <td style="width:91.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="121" nowrap="nowrap"> Key for period
</td> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap"> Value1
</td> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap"> Value2
</td> </tr> <tr style="mso-yfti-irow:1;height:15.0pt"> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">

1​

</td> <td style="width:91.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="121" nowrap="nowrap">

111​

</td> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">

90​

</td> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">

12​

</td> </tr> <tr style="mso-yfti-irow:2;height:15.0pt"> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">

1​

</td> <td style="width:91.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="121" nowrap="nowrap">

112​

</td> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">

80​

</td> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">

12​

</td> </tr> <tr style="mso-yfti-irow:3;height:15.0pt"> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">

1​

</td> <td style="width:91.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="121" nowrap="nowrap">

113​

</td> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">

70​

</td> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">

10​

</td> </tr> <tr style="mso-yfti-irow:4;height:15.0pt"> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">

1​

</td> <td style="width:91.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="121" nowrap="nowrap">

114​

</td> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">

60​

</td> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">

10​

</td> </tr> <tr style="mso-yfti-irow:5;height:15.0pt"> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">

2​

</td> <td style="width:91.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="121" nowrap="nowrap">

111​

</td> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">

80​

</td> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">

12​

</td> </tr> <tr style="mso-yfti-irow:6;height:15.0pt"> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">

2​

</td> <td style="width:91.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="121" nowrap="nowrap">

112​

</td> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">

70​

</td> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">

10​

</td> </tr> <tr style="mso-yfti-irow:7;height:15.0pt"> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">

2​

</td> <td style="width:91.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="121" nowrap="nowrap">

113​

</td> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">

60​

</td> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">

10​

</td> </tr> <tr style="mso-yfti-irow:8;mso-yfti-lastrow:yes;height:15.0pt"> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">

2​

</td> <td style="width:91.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="121" nowrap="nowrap">

114​

</td> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">

50​

</td> <td style="width:48.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="64" nowrap="nowrap">

8​

</td> </tr> </tbody></table>
If it assists, you could think of period 1 as Q1, period 2 as Q2 etc, and I wish to see how two periods vary in terms of their values, both in absolute numerical difference, but also % difference.

Sample un-pivoted results would therefore look something like this:

<table class="MsoNormalTable" style="width:457.35pt;margin-left:4.7pt;border-collapse:collapse;mso-yfti-tbllook: 1184;mso-padding-alt:0cm 5.4pt 0cm 5.4pt" width="610" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;height:15.0pt"> <td style="width:78.35pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="104" nowrap="nowrap"> Key for period
</td> <td style="width:45.35pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="60" nowrap="nowrap"> Value1
</td> <td style="width:45.35pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="60" nowrap="nowrap"> Value2
</td> <td style="width:62.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="83" nowrap="nowrap"> Value 1Diff
</td> <td style="width:62.8pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="84" nowrap="nowrap"> Value2Diff
</td> <td style="width:81.75pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="109" nowrap="nowrap"> Value1Percent
</td> <td style="width:81.75pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="109" nowrap="nowrap"> Value2Percent
</td> </tr> <tr style="mso-yfti-irow:1;height:15.0pt"> <td style="width:78.35pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="104" nowrap="nowrap">

111​

</td> <td style="width:45.35pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="60" nowrap="nowrap">

90​

</td> <td style="width:45.35pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="60" nowrap="nowrap">

12​

</td> <td style="width:62.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="83" nowrap="nowrap">

10​

</td> <td style="width:62.8pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="84" nowrap="nowrap">

0​

</td> <td style="width:81.75pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="109" nowrap="nowrap">

11%​

</td> <td style="width:81.75pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="109" nowrap="nowrap">

0%​

</td> </tr> <tr style="mso-yfti-irow:2;height:15.0pt"> <td style="width:78.35pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="104" nowrap="nowrap">

112​

</td> <td style="width:45.35pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="60" nowrap="nowrap">

80​

</td> <td style="width:45.35pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="60" nowrap="nowrap">

12​

</td> <td style="width:62.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="83" nowrap="nowrap">

10​

</td> <td style="width:62.8pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="84" nowrap="nowrap">

2​

</td> <td style="width:81.75pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="109" nowrap="nowrap">

13%​

</td> <td style="width:81.75pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="109" nowrap="nowrap">

17%​

</td> </tr> <tr style="mso-yfti-irow:3;height:15.0pt"> <td style="width:78.35pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="104" nowrap="nowrap">

113​

</td> <td style="width:45.35pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="60" nowrap="nowrap">

70​

</td> <td style="width:45.35pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="60" nowrap="nowrap">

10​

</td> <td style="width:62.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="83" nowrap="nowrap">

10​

</td> <td style="width:62.8pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="84" nowrap="nowrap">

0​

</td> <td style="width:81.75pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="109" nowrap="nowrap">

14%​

</td> <td style="width:81.75pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="109" nowrap="nowrap">

0%​

</td> </tr> <tr style="mso-yfti-irow:4;mso-yfti-lastrow:yes;height:15.0pt"> <td style="width:78.35pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="104" nowrap="nowrap">

114​

</td> <td style="width:45.35pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="60" nowrap="nowrap">

60​

</td> <td style="width:45.35pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="60" nowrap="nowrap">

10​

</td> <td style="width:62.0pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="83" nowrap="nowrap">

10​

</td> <td style="width:62.8pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="84" nowrap="nowrap">

2​

</td> <td style="width:81.75pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="109" nowrap="nowrap">

17%​

</td> <td style="width:81.75pt;padding:0cm 5.4pt 0cm 5.4pt; height:15.0pt" valign="bottom" width="109" nowrap="nowrap">

20%​

</td> </tr> </tbody></table>

I have seen the examples such as budget vs actual here: http://www.powerpivotpro.com/2012/01/salesbudget-integrating-data-of-different-grains/ however, it is not clear to me how to apply this logic to my problem.

It seems that my only option would be to bring each period back as a distinct table, and link them by the [KeyForPeriod] column in Powerpivot and compute the measures.

This seems to have a number of disadvantages below

1. Since I have tens of “value” column fields it seems very onerous to create a “diff” column and a “percentage diff” column for each value upon import of new data.
2. The import of periods (ie 1 through n) will have to be performed by the user (although I expect we could write some VBA to perform this).
3. Such a result could be done with a pivot table. What use is powerpivot here?

Are there any other solutions to the problem?
Note, I have full control over the database, so if it is possible we can generate a more useful schema for input.

Best regards and thanks for your answers in advance.
Topdown.

 

[powerpivotpro]

Clarifying q's:

1) Is the combination of Period and KeyForPeriod unique? Meaning, will there only ever be one row of data that is Period=1 and KeyForPeriod=111?

2) How many values of Period will there be? Just 1 and 2, or lots of values?

 

[Topdown]

Hi Rob, I feel privileged for having you look into my question!

1) Is the combination of Period and KeyForPeriod unique? Meaning, will there only ever be one row of data that is Period=1 and KeyForPeriod=111?

Yes, this will be unique.

2) How many values of Period will there be? Just 1 and 2, or lots of values?

Potentially lots of values.


It might help to give you some context (the term "period" was used in order to be more familiar for readers).

We are running some modelling whereby all the records indexed by KeyPerPeriod are run once, and then run a n'th time that might produce different results. I wish to compare the output of two executions.

Therefore, for each period/execution we can be guaranteed that the number of records, and the keyForPeriod in each period/execution exist in each period set.

The number of value fields that we wish to compare is also large (20+).

Many thanks again!

 

[powerpivotpro]

I think we have worked this out "offline" and my blog post tomorrow will cover it. I will link to the post here once it is up

 

[powerpivotpro]

OK, the solution I provided is now the subject of a blog post, including a link to download the workbook

http://www.powerpivotpro.com/2012/01/comparing-scientific-and-other-data-across-trials/