Dealing with (multiple) many2many relationships in Powerpivot

[bratwoorst]

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

Product	Category
P1	C1
P1	C2
P2	C2

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

Category / 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 A mystifying and awesome solution for many 2 many - PowerPivotPro and SQLBI - Marco Russo : Many-to-Many relationships in PowerPivot, but with the time dimension I was not able to produce the intended solution. Can anybody enlighten me?

 

[bratwoorst]

There is a mistake in the first table, P2 should belong to C1 instead of C2.