• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

PowerPivot - Relationship not working

Alexis

Member
Hi everyone

I have loaded a couple of tables into PowerPivot and created a relationship on a unique reference number (URN) column which exists in both tables.

One table contains how much schools spent on certain things in a given year, the other contain's my company's sales to schools in that year. I want to see how much of their total spend was with me.

When I create the pivot table, it says there is no relationship and to create one (even though I have), so I do so, but it doesn't work.

I searched it on the web and found Rob Collie's site and got this page...
http://www.powerpivotpro.com/2013/06/04/

It does say that look up tables are not meant for numeric values. Fine. But I tried the =RELATED() function too and that doesn't work either.

Does anyone have any experience of this and how I fix it?

Many thanks
Alexis
 
Just an additional note..
I have done this exercise before with loads of lookups to create a one big table with all the fields I would need and then created a pivot on top, but wanted to avoid that by using PowerPivot.

I am up to Module 6 on the PowerPivot classes and want to be able to use my newly acquired skills!!

Thanks again
Alexis
 
I've attached a very simplified example of data I'm working with, if that helps at all.

We know what a customer total budget is (because they are a public body), and I want to know what % of their budget we have supplied (market share).

It works if I have the customer ID in the row field, but I want to do it based on my branches. This is where the error "Relationship may be needed" occurs, and then it says it cannot detect one.

The real data has a lot more columns, but essentially this is what I want to do, and I wanted to join on customerID.

Even if someone can tell me they cannot help or point me somewhere else that would be helpful.

Thanks
Alexis
 

Attachments

  • Relationship may be needed.xlsx
    260.9 KB · Views: 0
Back
Top