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
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Week[/TD]
[TD]Month[/TD]
[TD]Quarter[/TD]
[TD]Year[/TD]
[TD]Client[/TD]
[TD]Line Item[/TD]
[TD]LI ID[/TD]
[TD]LI Type[/TD]
[TD]LI Payout[/TD]
[TD]Tier[/TD]
[TD]Impressions[/TD]
[TD]Clicks[/TD]
[TD]Spent[/TD]
[/TR]
[TR]
[TD]12/1/12[/TD]
[TD]48[/TD]
[TD]12[/TD]
[TD]4[/TD]
[TD]2012[/TD]
[TD]Dog[/TD]
[TD]Bonez[/TD]
[TD]5[/TD]
[TD]CPC[/TD]
[TD]Clicks[/TD]
[TD]2[/TD]
[TD]2,564,152[/TD]
[TD]26,454[/TD]
[TD]12,382.99[/TD]
[/TR]
[TR]
[TD]12/1/12[/TD]
[TD]48[/TD]
[TD]12[/TD]
[TD]4[/TD]
[TD]2012[/TD]
[TD]Cat[/TD]
[TD]Fishez[/TD]
[TD]6[/TD]
[TD]CPC[/TD]
[TD]Clicks[/TD]
[TD]1[/TD]
[TD]315,564[/TD]
[TD]14,548[/TD]
[TD]9,549.98[/TD]
[/TR]
[TR]
[TD]12/2/12[/TD]
[TD]48[/TD]
[TD]12[/TD]
[TD]4[/TD]
[TD]2012[/TD]
[TD]Dog[/TD]
[TD]Bonez[/TD]
[TD]5[/TD]
[TD]CPC[/TD]
[TD]Clicks[/TD]
[TD]2[/TD]
[TD]1,489,112[/TD]
[TD]14,954[/TD]
[TD]8,464.57[/TD]
[/TR]
[TR]
[TD]12/2/12[/TD]
[TD]48[/TD]
[TD]12[/TD]
[TD]4[/TD]
[TD]2012[/TD]
[TD]Cat[/TD]
[TD]Fishez[/TD]
[TD]6[/TD]
[TD]CPC[/TD]
[TD]Clicks[/TD]
[TD]1[/TD]
[TD]545,972[/TD]
[TD]17,361[/TD]
[TD]9,834.11[/TD]
[/TR]
</tbody>[/TABLE]


Table2 -- No unique Columns/Fields
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Line Item[/TD]
[TD]LI ID[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Budget[/TD]
[TD]Payout[/TD]
[TD]Payout Percent[/TD]
[TD]Fee[/TD]
[TD]Goal[/TD]
[/TR]
[TR]
[TD]Bonez[/TD]
[TD]5[/TD]
[TD]2/2/12[/TD]
[TD]12/3/12[/TD]
[TD]$600,000[/TD]
[TD]$1.50[/TD]
[TD][/TD]
[TD]$5,000[/TD]
[TD]1,000,000[/TD]
[/TR]
[TR]
[TD]Fishez[/TD]
[TD]6[/TD]
[TD]8/9/12[/TD]
[TD]12/31/12[/TD]
[TD]$400,000[/TD]
[TD]$1.12[/TD]
[TD][/TD]
[TD][/TD]
[TD]800,000[/TD]
[/TR]
[TR]
[TD]Bonez[/TD]
[TD]5[/TD]
[TD]12/3/12[/TD]
[TD]3/1/13[/TD]
[TD]$200,000[/TD]
[TD]$1.40[/TD]
[TD][/TD]
[TD][/TD]
[TD]300,000[/TD]
[/TR]
[TR]
[TD]Purple[/TD]
[TD]7[/TD]
[TD]2/14/13[/TD]
[TD]5/1/13[/TD]
[TD]$100,000[/TD]
[TD][/TD]
[TD].12[/TD]
[TD]$5,000[/TD]
[TD]150,000[/TD]
[/TR]
</tbody>[/TABLE]


Table3 -- Unique lookup table to create a relationship between 1 and 2
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]LI ID[/TD]
[/TR]
[TR]
[TD]5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[/TR]
[TR]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]



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:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Client[/TD]
[TD]LI ID[/TD]
[TD]LI Type[/TD]
[TD]LI Payout[/TD]
[TD]Tier[/TD]
[TD]Budget[/TD]
[TD]Payout[/TD]
[TD]PayoutPercent[/TD]
[TD]Fee[/TD]
[TD]Goal[/TD]
[TD]StartDate[/TD]
[TD]EndDate[/TD]
[TD]Impressions[/TD]
[TD]Clicks[/TD]
[TD]Spent[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]5[/TD]
[TD]CPC[/TD]
[TD]Clicks[/TD]
[TD]2[/TD]
[TD]200,000[/TD]
[TD]1.40[/TD]
[TD][/TD]
[TD][/TD]
[TD]300,000[/TD]
[TD]12/3/12[/TD]
[TD]3/1/13[/TD]
[TD]1,489,112[/TD]
[TD]14,954[/TD]
[TD]8,464.57[/TD]
[/TR]
</tbody>[/TABLE]

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.