none
Pivot table based on powerpivot problem excel 2010

    Question

  • Hello

    I have 2 tables with relationships.

    one contains purchases with a supplier ID (and some other details). each supplier can, of course, appear several times.

    the other one contains supplier's ID and name

    I tried to create a simple pivot table that takes the Suplliers details from the purchase table and the supllier name from the suppliers table (all in the row lables, no calculations at the values area)

    The result was that for each purchase ID I got all the suppliers name (cartesian table).

    Now, I know that the relationד are OK, because when I trieג to check with the Related function, I got the correct answer, and when I based my pivot table on one table only (the purchases using the calculated column with the related function) the pivot table appeared OK.

    By the way, it happend on 2 different computers with excel 2010 on XP and excel 2010 on windows 7,

    and also on different databases

    What am I missing here?

    Thanks

    Sunday, February 09, 2014 9:47 AM

Answers

All replies

  • Hi MaYaLi,

    This does sound quite unusual. Are you able to create a simplified version of this workbook with anonymised data and upload it to skydrive so that we can take a look?

    Regards,

    Michael

    Sunday, February 09, 2014 9:18 PM
  • Hi 

    I attach here a link to a sample workbook

    It contains 2 tables: Nm - that contains the supplier name and ID

    Fact - that represents the sales (Supplier ID and quantity)

    I would expect to have a pivot like the one that starts at column G (which I did manually),

    and not like the one that starts in column A

    The link:

    https://www.dropbox.com/s/9mbk0wg95hj8nxm/Pivot.xlsx

    Thanks

    MayaLi

    Monday, February 10, 2014 6:50 PM
  • Hi MayaLi,

    Can you confirm that this relationship exists:

    I had to create it after downloading the file and everything looked fine afterwards...

    Granted I'm using Excel 2013 and had to upgrade the model in order to open it. I have access to a PC with Power Pivot V1 installed that I can check this on tomorrow morning. If you had already created the above relationship, then this is definitely very strange. Is installing the latest version of Power Pivot an option for you or are you restricted to using V1?

    Michael



    Monday, February 10, 2014 8:56 PM
  • Just tried opening it on a PC with Power Pivot V1 but it's telling me that it was created in a newer version. I'm also seeing what appear to be Hebrew characters for subtotals and totals...

    Do you know what version of Power Pivot you are using? You should be able find this under 'Power Pivot' tab > Settings

    Tuesday, February 11, 2014 9:35 AM
  • It seems to be a bug, since I had the same message:

    http://www.powerpivotpro.com/2013/05/relationship-may-be-needed-but-i-already-have-a-relationship-whats-going-on/

    The strange thing is that I did have relationships (I double checked it), and... now they are gone

    Thank you anyway

    Great forum (:

    Wednesday, February 12, 2014 9:45 PM
  • And as for the version- I have excel 2010 on XP, and could not find the version

    I also have excel 2010 on windows 7, but it is closed now (:

    Anyway - I had the same problem on both computers...

    Wednesday, February 12, 2014 9:48 PM
  • If it is feasible to do so, you could ask (assuming it's a work PC) for the latest Power Pivot for Excel 2010 version to be installed on one of your available PCs and check to see if this resolves the issue: http://www.microsoft.com/en-us/download/details.aspx?id=29074.

    Thursday, February 13, 2014 7:27 AM