0

I have a question about how to deal with multiple many2many relationships in Powerpivot. Here is my scenario: I have a bunch of products, where each product is assigned to at least one but potentially more categories, so for example my first table is

Products | Category  
   P1    |    C1  
   P1    |    C2  
   P2    |    C1

Next I have a table with revenues per product and season (time)

Product | Season | Revenue  
  P1    | 201606 |   10  
  P1    | 201607 |   20  
  P2    | 201606 |   15  
  P2    | 201607 |   25

What I want is to create a measure so that my revenues can be divided by time as well as category (therefore I would create to additional tables with the time and category hierarchy). When fully expanded my final result should look like

Cat / Season | 06 | 07 | 2016  
    C        | 25 | 45 |  70  
    1        | 25 | 45 |  70  
    2        | 10 | 15 |  25

When I do not have the time dimension I have found a solution, for example http://www.powerpivotpro.com/2012/11/a-mystifying-and-awesome-solution-for-many-2-many and http://sqlblog.com/blogs/marco_russo/archive/2009/12/07/many-to-many-relationships-in-powerpivot.aspx, but with the time dimension I was not able to produce the intended solution. Can anybody enlighten me?

bratwoorst711
  • 77
  • 1
  • 4
  • How do you calculate 25 for `Cat 1` and column `06`? Explain in detail how the final result is calculated. – alejandro zuleta Aug 10 '16 at 14:41
  • @alejandrozuleta Unfortunately there was a mistake in the first table, product 2 should belong to category C1. I've corrected the post, I hope now the final result is clear. If not please let me know. – bratwoorst711 Aug 11 '16 at 05:42

1 Answers1

0

As you saw in the posts you added in the question, it is necessary to create helper tables for modeling M-to-M relationships.

Create a table called DistinctProduct with one column called Product that holds the distinct products.

enter image description here

In the Revenue table create two columns called Month and Year. Also create a measure called TotalRevenue.

Month = RIGHT(RevenueTable[Season],2) 

Year = LEFT(RevenueTable[Season],4)

TotalRevenue = SUM(Revenue[Revenue])

Then just relate the tables as shown in this E-R diagram:

enter image description here

Once this model is done you can use a pivot table, add Month column to pivot table column and Category to rows and it will produce a table similar to this.

enter image description here

Let me know if this helps.

alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
  • Unfortunately it did not work, see ![Model](http://imgur.com/a/aD2Jg). Actually, when adding the category hierarchy to the report Excel complained about a missing connection although the two tables are conncected via the Single Product table. – bratwoorst711 Aug 12 '16 at 10:11
  • @bratwoorst711, Try adding the category instead of the hierarchy. I don't know how your hierarchy is structured so I cannot figure out why your issue keeps. – alejandro zuleta Aug 12 '16 at 16:50
  • I have tried to use the category instead, the problem remains. Fyi the category is quite simple, C1 has Level 1 "C" and Level 2 "1" and C2 has Level 1 "C" and Level 2 "2". – bratwoorst711 Aug 15 '16 at 08:45