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!

 

[miguel.escobar]

I'm pretty sure that this thread will help you A LOT
http://www.mrexcel.com/forum/powerpivot-questions/673511-linked-table-info-used-calculate-column.html

It's pretty much the same scenario. I'm working on a scenario so I can upload a file as well later.

Happy holidays!
Miguel

 

[miguel.escobar]

Man...This is probably the fastest I've answered a post in my life...

Check out that your DIM table (the one with the Id's for LI) is missing the TYPE column to show if it's for percent or if there's a CPC/CPF for it so I've created that column and put on some values in it.
What I did was:
- Act as Santa for 2 minutes while wearing a xmas hat
- Created calculated tables on the fact table to show the corresponding %percent and fee

Here's the file: https://dl.dropbox.com/u/54063091/Case.xlsx

I'm missing the:

=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]))))

as I think that you should create a new calculated column instead of a measure (easy way) so you can get a column of the aggregate that you're trying to come up with.

Let me know if this xmas present is good enough! There's still time as Santa's still wearing the hat

Miguel

 

[Mfrosty]

Thank you so much! Let me read through these and see what you did, but I hope this is the solution I have been looking for! Thanks Santa!

Edit: Just read through your calculations and they look spot on!

So just to re-cap: I basically want to just go through and create new calculatedcolumns in table 1 to bring over the values from table 2 after I have created a relationship between the two using table 3? Thank you!

 

[Mfrosty]

Sorry still having an issue:

"Calculation error in column 'daily'[]: A table of multiple values was supplied where a single value was expected."


edit: Nevermind mystery solved. Was missing a relationship! Thanks Santa!

 

[miguel.escobar]

I was missing the last part but now you made it crystal clear! Instead of me doing the final solution, why don't you try using the columns now that you have them available in 1 table.

Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.

In the event that you encounter any problems just post it here.

Best!
Miguel

 

[miguel.escobar]

Sorry still having an issue:

"Calculation error in column 'daily'[]: A table of multiple values was supplied where a single value was expected."


edit: Nevermind mystery solved. Was missing a relationship! Thanks Santa!

Awesome! glad it worked.

Happy holidays!
Miguel

 

[Mfrosty]

Santa don't go just yet!

One more question. I am getting a calculation error when I try to migrate budget over. Here is my formula:

=CALCULATE(VALUES(payout_table[Budget]),FILTER(RELATEDTABLE(payout_table), [Date]>=payout_table[Start Date]),FILTER(RELATEDTABLE(payout_table), [Date]<=payout_table[End Date]))

Error: Calculation error in column 'Table1'[]: A table of multiple values was supplied where a single value was expected.

I assume I need to filter by something else to find a specific budget amt unique to a LI id?

 

[Mfrosty]

So I switched the VALUES() function to SUM() and it seems to work. It does product duplicates, but I can work around that.

 

[miguel.escobar]

Hey frosty!

I'm on my phone right now but it seems that you are right, there are duplicates and that's why it can't give you just 1 distinct value in return. Could you upload the workbook with the formula that you are trying and what you're trying to accomplish?

Not the file that I created but your actual model so we can work with all the dependents.

Best!
Miguel a.k.a. Santa