1

Longtime answer-seeker, first time question-asker here so I'm open to feedback about how I'm asking as well. I'm relatively new to Excel's PowerPivot but feel like I have a handle on it for the most part.

I am using PowerPivot for Excel 2010. I have data that I only receive weekly totals for and I use the monday of that week as my primary key in the table I call 'WeeklyTracking'. I create a relationship from that to my Date Table so that I can filter/analyze by month, year, etc. I get no error when I make that relationship, it is a one to many ( I checked for duplicates in my WeeklyTracking table), and it is showing as 'active'.

However, when I go to create a pivot table it's not separating the data by my Date Table fields. It simply repeats the total for the column. What my pivot table shows me. Table Relationships

I tired disconnecting all other table relationships, and I even tried converting dates to numeric values and linking those but to no avail. When I choose to make column labels the date within the 'WeeklyTracking' table it separates out by date just fine which leads me to believe it has to do with the relationship. But I did something very similar with data I get monthly and didn't have any problems so I can't figure out what's different.

Any ideas?

EDIT: It's actually not working for my monthly report either, upon closer inspection. But still I dont understand why not--There's a primary key in each table...

UPDATE: Tried creating a ID number using a formula for each week and creating the relationship on that and it didn't work either.

R Dean
  • 11
  • 1
  • 3
  • I'll say upfront I don't know a lot about pivot tables. One clue would help: what is the number 335? is it the true grand total across all years? Somehow your report doesn't have scope on 'year' - I'm guessing it's just finding the grand total and repeating against year because it doesn't know how it relates. What is the primary key of your date table? If you have a date table that goes down to days, that will be a problem - your data table would need to be unique on a week. – Nick.Mc Oct 29 '16 at 22:10
  • Can you provide a screenshot of the relationships? Interested in which direction the relationship runs. – GregGalloway Oct 30 '16 at 00:20
  • Thanks for the quick replies! @Nick.McDermaid, You're right, 355 is the total of the column. It's just repeating the same total instead of calculating it by the year which is suppose to be dictated by the relationship. primary key of my date table is the day, like yours but i use a separate column to associate it by week. It's worked fine before when I do the same thing for monthly data which is why this is all so confusing. – R Dean Nov 02 '16 at 19:31
  • @GregGalloway , I'll edit the original post to include that image of the relationship. – R Dean Nov 02 '16 at 19:36
  • I was wrong--it's also not working for my monthly data. But I still don't understand why it shouldn't. – R Dean Nov 02 '16 at 19:48
  • @RDean you need to reverse the relationships. The arrows should point to the Date table. Hopefully that fixes it. – GregGalloway Nov 02 '16 at 22:20
  • @GregGalloway, the arrow is the 1 and the black dot is the many? Am I reading those symbols right? (seriously, I'm not positive on that). If so, then the relationships are correct. Since Date is the primary key in the DateTable that means that Weeks repeat themselves 7 times, once per each date within a week, in my DateTable--making that the 'many' side of the relationship. While my WeeklyTracking table only has one row per week--making that the 'one' side of my relationship. – R Dean Nov 11 '16 at 18:40
  • @RDean there is one row per day in the DateTbl. So the arrow needs to point to that (to the primary key and away from the foreign key). Please try it. – GregGalloway Nov 11 '16 at 19:27

0 Answers0