PowerPivot Calculated Fields Across Multi Tables + Relationship Creation

[Mfrosty]

Happy Holidays Mr.Excel Forums!

The only present I want from Santa this year is the answer to these two problems.. Lame jokes aside, let's begin..

I am creating a dashboard with PowerPivot that is being driven from our MySQL db. Due to issues with the odbc connection driver I decided to store the data locally into excel files and connect powerpivot to that.

My two queries produce the following table structure in PowerPivot:

Table1 -- No unique Columns/Fields

Date	Week	Month	Quarter	Year	Client	Line Item	LI ID	LI Type	LI Payout	Tier	Impressions	Clicks	Spent
12/1/12	48	12	4	2012	Dog	Bonez	5	CPC	Clicks	2	2,564,152	26,454	12,382.99
12/1/12	48	12	4	2012	Cat	Fishez	6	CPC	Clicks	1	315,564	14,548	9,549.98
12/2/12	48	12	4	2012	Dog	Bonez	5	CPC	Clicks	2	1,489,112	14,954	8,464.57
12/2/12	48	12	4	2012	Cat	Fishez	6	CPC	Clicks	1	545,972	17,361	9,834.11

<tbody>
</tbody>

Table2 -- No unique Columns/Fields

Line Item	LI ID	Start Date	End Date	Budget	Payout	Payout Percent	Fee	Goal
Bonez	5	2/2/12	12/3/12	$600,000	$1.50		$5,000	1,000,000
Fishez	6	8/9/12	12/31/12	$400,000	$1.12			800,000
Bonez	5	12/3/12	3/1/13	$200,000	$1.40			300,000
Purple	7	2/14/13	5/1/13	$100,000		.12	$5,000	150,000

<tbody>
</tbody>

Table3 -- Unique lookup table to create a relationship between 1 and 2

LI ID

5

6

7

<tbody>
</tbody>

So that's what they look like. Query 1 groups by day & query 2 groups by almost all the fields to capture when a change in a line item occurs (new date, new budget, etc.)

Onto my 1st question..

I tried to create several calculated fields and was being met with this lovely error at every corner:

Calculation error in measure 'Table1'[Events to Date]: The value for column 'LI Type' in table 'line_item_total' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified.

For example I was trying to create a calculated field that would show the "events to date":

=IF(Table1[LI Type]= "percent", sum(daily[Spent])*(1+Table2[Payout Percentage]),IF(Table1[LI Type]= "CPC", sum(Table1[Clicks]), if(Table1[LI Type]= "CPF", sum(Table1[Connections]))))

I was reading through misc. forums about creating a new calculated field for each calculation first and wrapping it in 'CALCULATE()', but I didn't have any luck with that either. What am I missing here?



Onto my 2nd question..

What additional relationships or tables do I need to add so that I can create a PivotTable that combines Tables 1 & 2?
Example:

Client

LI ID

LI Type

LI Payout

Tier

Budget

Payout

PayoutPercent

Fee

Goal

StartDate

EndDate

Impressions

Clicks

Spent

Dog

5

CPC

Clicks

2

200,000

1.40

300,000

12/3/12

3/1/13

1,489,112

14,954

8,464.57

<tbody>
</tbody>

And ultimately be able to splice this data by the Table 1 date field so I can see the line item progress over time.

Some material I read that "may" have my answer hidden in it: User-Friendly Report Sorting With Slicers! « PowerPivotPro

Sorry for the long post, and thanks in advance for any help/insights!

 

[Mfrosty]

Sure thing Miguel. Give me a few to get it organized. Just got home from work. I will pm you the dropbox link.