Can Powerpivot do this?

[JEB85]

I have Two Tables:</SPAN>
Database</SPAN>
Calendar - (includes 'Date', 'FiscalQTR', 'FiscalYR' and 'Period')</SPAN>

In the Database Table I have the following fields:</SPAN>
Contract</SPAN>
Code1</SPAN>
Code2</SPAN>
Reporting Month e.g. Sep-11, Oct-11, Nov-11 etc</SPAN>

I've created a relationship between 'Reporting Month' and 'Date' (Daily Dates in the 'Calendar' table) so in my pivot table I use FiscalYr and Period</SPAN>

What I want to be able to do, and I'm not sure if this is possible, is create a pivot table which will summarises the movement in position of our contracts</SPAN>

At the minute I have lookup formulas to the side of my pivot tables to establish a 'TYPE' of movement which basically say:</SPAN>

New = New Product on a new contract</SPAN>
Lost = Lost Product on a lost contract</SPAN>
Positive Churn = New Product on an existing contract</SPAN>
Negative Churn = Lost Product on an existing contract</SPAN>
Contract Transfer = Product transferred to different contract</SPAN>
Continuous = No movement</SPAN>

I then summarise the position through some sumproduct calculations.</SPAN></SPAN>

Example:</SPAN></SPAN>

	Fiscal Yr</SPAN></SPAN>	2011-12</SPAN></SPAN>				Fiscal Yr</SPAN></SPAN>	2011-12</SPAN></SPAN>
	Period:</SPAN></SPAN>	Sep-11</SPAN></SPAN>				Period:</SPAN></SPAN>	Oct-11</SPAN></SPAN>
	Region:</SPAN></SPAN>	W</SPAN></SPAN>				Region:</SPAN></SPAN>	W</SPAN></SPAN>
TYPE</SPAN></SPAN>	Contract</SPAN></SPAN>	Code1</SPAN></SPAN>	Code2</SPAN></SPAN>		TYPE</SPAN></SPAN>	Contract</SPAN></SPAN>	Code1</SPAN></SPAN>	Code2</SPAN></SPAN>
Continuous</SPAN></SPAN>	A</SPAN></SPAN>	001B</SPAN></SPAN>	ZZZ</SPAN></SPAN>		Continuous</SPAN></SPAN>	A</SPAN></SPAN>	001B</SPAN></SPAN>	ZZZ</SPAN></SPAN>
Contract Transfer</SPAN></SPAN>	A</SPAN></SPAN>	002C</SPAN></SPAN>	ZZZ</SPAN></SPAN>		Continuous</SPAN></SPAN>	A</SPAN></SPAN>	003M</SPAN></SPAN>	ZZZ</SPAN></SPAN>
Continuous</SPAN></SPAN>	A</SPAN></SPAN>	003M</SPAN></SPAN>	ZZZ</SPAN></SPAN>		Contract Transfer</SPAN></SPAN>	B</SPAN></SPAN>	002C</SPAN></SPAN>	ZZZ</SPAN></SPAN>
Negative Churn</SPAN></SPAN>	A</SPAN></SPAN>	004Y</SPAN></SPAN>	YYY</SPAN></SPAN>		Continuous</SPAN></SPAN>	B</SPAN></SPAN>	399B</SPAN></SPAN>	CCK</SPAN></SPAN>
Continuous</SPAN></SPAN>	B</SPAN></SPAN>	399B</SPAN></SPAN>	CCK</SPAN></SPAN>		Continuous</SPAN></SPAN>	B</SPAN></SPAN>	486B</SPAN></SPAN>	PPO</SPAN></SPAN>
Continuous</SPAN></SPAN>	B</SPAN></SPAN>	486B</SPAN></SPAN>	PPO</SPAN></SPAN>		Positive Churn</SPAN></SPAN>	B</SPAN></SPAN>	899P</SPAN></SPAN>	AAM</SPAN></SPAN>
Continuous</SPAN></SPAN>	C</SPAN></SPAN>	070E</SPAN></SPAN>	ABC</SPAN></SPAN>		Continuous</SPAN></SPAN>	C</SPAN></SPAN>	070E</SPAN></SPAN>	ABC</SPAN></SPAN>
Continuous</SPAN></SPAN>	C</SPAN></SPAN>	071T</SPAN></SPAN>	ABB</SPAN></SPAN>		Continuous</SPAN></SPAN>	C</SPAN></SPAN>	071T</SPAN></SPAN>	ABB</SPAN></SPAN>
Negative Churn</SPAN></SPAN>	C</SPAN></SPAN>	996Y</SPAN></SPAN>	PQR</SPAN></SPAN>		New</SPAN></SPAN>	D</SPAN></SPAN>	787U</SPAN></SPAN>	TTE</SPAN></SPAN>
Lost</SPAN></SPAN>	G</SPAN></SPAN>	998Y</SPAN></SPAN>	PQT</SPAN></SPAN>		New</SPAN></SPAN>	D</SPAN></SPAN>	799Y</SPAN></SPAN>	TTV</SPAN></SPAN>
					New</SPAN></SPAN>	D</SPAN></SPAN>	802R</SPAN></SPAN>	UHU</SPAN></SPAN>
	Oct-11</SPAN></SPAN>
New</SPAN></SPAN>	3</SPAN></SPAN>
Lost</SPAN></SPAN>	1</SPAN></SPAN>
Positive Churn</SPAN></SPAN>	1</SPAN></SPAN>
Negative Churn</SPAN></SPAN>	2</SPAN></SPAN>
Contract Transfer</SPAN></SPAN>	1</SPAN></SPAN>
Continuous</SPAN></SPAN>	6</SPAN></SPAN>

<TBODY>
</TBODY>

The formulas for last month (in this case Sept-11) will only ever deliver 'Continuous', 'Contract Transfer', 'Negative Churn' or 'Lost'</SPAN></SPAN>
The formulas for the current month (in this case Oct-11) will only ever deliver 'Continuous', 'Contract Transfer', 'Positive Churn' and 'New'</SPAN></SPAN>
The 'Continuous' and 'Contract Transfer' for both months will always equal each other</SPAN></SPAN>

('Code1' relates to the type of product and 'Code2' is a unique reference for that product)</SPAN></SPAN>

At the minute I'm doing all of this through 2 pivot tables and having to change both the 'Period' and the 'Region' to update my calculations</SPAN></SPAN>

Is it possible to create formulas in Powerpivot which will enable me to summarise the position through one PowerPivot (having all the periods in the columns and the type as rows with FiscalYr, Period and Region as filters)?</SPAN></SPAN>

Thanks

 

[llenza]

Not 100% sure of what you are looking for but here I go!
If I understand somewhat you want to have all the data in one place
For example:

Fiscal Yr	Period:	Region:	TYPE	Contract	Code1	Code2
2011-12	Sep-12	W	Continuous	A	001B	ZZZ
2011-12	Sep-12	W	Contract Transfer	A	002C	ZZZ
2011-12	Sep-12	W	Continuous	A	003M	ZZZ
2011-12	Sep-12	W	Negative Churn	A	004Y	YYY
2011-12	Sep-12	W	Continuous	B	399B	CCK
2011-12	Sep-12	W	Continuous	B	486B	PPO
2011-12	Sep-12	W	Continuous	C	070E	ABC
2011-12	Sep-12	W	Continuous	C	071T	ABB
2011-12	Sep-12	W	Negative Churn	C	996Y	PQR
2011-12	Sep-12	W	Lost	G	998Y	PQT
2011-12	Oct-12	W	Continuous	A	001B	ZZZ
2011-12	Oct-12	W	Continuous	A	003M	ZZZ
2011-12	Oct-12	W	Contract Transfer	B	002C	ZZZ
2011-12	Oct-12	W	Continuous	B	399B	CCK
2011-12	Oct-12	W	Continuous	B	486B	PPO
2011-12	Oct-12	W	Positive Churn	B	899P	AAM
2011-12	Oct-12	W	Continuous	C	070E	ABC
2011-12	Oct-12	W	Continuous	C	071T	ABB
2011-12	Oct-12	W	New	D	787U	TTE
2011-12	Oct-12	W	New	D	799Y	TTV
2011-12	Oct-12	W	New	D	802R	UHU

<tbody>
</tbody>

Now that all the data is on one sheet, create a pivot table. I have tested your sheet on my PC.
You can setup the Pivot Table to give you the following:
1. Summarize by Fiscal Yr (all or one or some)
2. Summarize by Period (all dates or one date or some dates)
3. Summarize by Region (all or one or some)
4. Summarize by Type and add types together with an overall amount
5. Illustrate Code1 and Code2 and add types together with an overall amount
6. Count Types only while maintaining the Fiscal Yr, Period and Region
See below:

Count of Contract
Fiscal Yr	Period:	Region:	Code1	Code2	TYPE	Total
2011-12	Sep-12	W	001B	ZZZ	Continuous	1
			002C	ZZZ	Contract Transfer	1
			003M	ZZZ	Continuous	1
			004Y	YYY	Negative Churn	1
			070E	ABC	Continuous	1
			071T	ABB	Continuous	1
			399B	CCK	Continuous	1
			486B	PPO	Continuous	1
			996Y	PQR	Negative Churn	1
			998Y	PQT	Lost	1
Grand Total						10

<tbody>
</tbody>

Count of Contract
Fiscal Yr	Period:	Region:	Code1	Code2	TYPE	Total
2011-12	Oct-12	W	001B	ZZZ	Continuous	1
			002C	ZZZ	Contract Transfer	1
			003M	ZZZ	Continuous	1
			070E	ABC	Continuous	1
			071T	ABB	Continuous	1
			399B	CCK	Continuous	1
			486B	PPO	Continuous	1
			787U	TTE	New	1
			799Y	TTV	New	1
			802R	UHU	New	1
			899P	AAM	Positive Churn	1
Grand Total						11

<tbody>
</tbody>

Not sure what you needed as the final product. Illustrated above is one pivot table for Oct and Sep. The Pivot Table can also combine the two. I did this on Excel 2000, but it would be easier in 2007 or 2010 with Table Link, so you can continue to add data and it calculates with
the formulas it already has. Good Luck!

 

[JEB85]

Thanks Llenza, my issue is creating the lookup formulas within the powerpivot grid, if I could do that there wouldn't be a problem. At the minute I'm just creating the lookup formulas in regular excel and so I'm limited to doing one month at a time!

 

[llenza]

Breakdown of a VLOOKUP formula:

Cell A3 =VLOOKUP(B3,E1:N16,2,0)

This is a test to learn to create VLOOKUP

Please the formula where you want the results to go
B3 represent the value to be matched (must be unique as it will stop looking when it finds it once)
E1:N16 represent the range of cells or all the cells in the table
2 represent the placement within the table

For example: when you have a table like E1 to N16. Column E is one, col. F(2) G(3) H(4) I(5) J(6) K(7) L(8) M(9) N(10)

Now, the number 2 represent not only the F Col. it also indicated that you want the VLOOKUP to find this value and compare it to B3

0 represents that you want an exact MATCH if Col 2 = B3 then you results will appear on A3

If I just confused you more let me know I will include table with this explaination. Good Luck! it took me a while to understand it myself. But you can do it!