4

I have two simple table in Excel 2010 - Products and Sales: enter image description here

I then linked them into PowerPivot - and here created the relationship from Sales.ProductId to Products.Id - like this:

enter image description here

Now I'm trying to build a Pivot that for each productId in the Sales table also shows me the Category and the PurchasePrice from the ProductTable. However, here I'm stuck - because the relationship is somehow faulty. When configuring the Pivot, I get a message Relationship may be needed even if the relationship already exists. The resulting - wrong - pivot table looks like this:enter image description here

though I'm actually trying to achieve this: enter image description here

I know that I could create calculated columns in the Sales table of PowerPivot and pull all required data using the RELATED DAX function - but as the real world project requires a lot of joins and fields I'm hoping that there's a better solution than this workaround...

I uploaded the example file here.

Peter Albert
  • 16,917
  • 5
  • 64
  • 88

1 Answers1

1

Peter,

Your question was a bit more tricky than I first though and I spent quite some time digging around, but I think I found the solution after reading this article about making distinct count in related tables.

I have updated your source file (so that Sales table contains more than 1 sales per product) and uploaded it to my public Dropbox folder.

You can see that I created 4 new calculated measures to illustrate my solution and to make it a bit easier to understand (Excel 2010 terminology, in 2013 it's Calculated Field):

Sales Price Total

=SUM(Sales[SalesPrice])

Products Sold

=COUNT(Sales[ProductId])

Purchase Price per Item

=CALCULATE(SUM(Products[PurchasePrice]),Sales)

Purchase Price Total

=[Purchase Price per Item] * [Product Sold]

The key difference here is the formula for calculating purchase price per item. The reason why you should SUM the product purchase price within CALCULATE function is explained in great detail in the linked article (even though in a bit different context):

In this way, any filter active on Sales (the related table) propagates to the lookup table.

There might be some other parts I missed, but I have tried this in couple other examples and it simply works as it should:

enter image description here

Petr Havlik
  • 3,307
  • 1
  • 19
  • 17
  • 1
    Hmmmm, confusing.m and I thought I found the answer :) Let me check that today and I will update my answer as soon as I can. – Petr Havlik Mar 11 '14 at 06:35
  • 1
    Peter, I have rewrote my answer completely, hopefully now it will be helpful and provide you with the solution. It took me some time to realize this and it's a bit misleading how relationship in PowerPivot works... – Petr Havlik Mar 12 '14 at 09:20
  • Thanks a lot - this looks really interesting, will go through it in detail later today! +1 already :-) – Peter Albert Mar 12 '14 at 09:58
  • Glad I could help Peter. If you are happy with the answer, could you please mark my answer as accepted? Thanks! – Petr Havlik Mar 12 '14 at 10:11