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

[table="width: 200, class: grid, align: left"]
[tr]
[td]Product[/td]
[td]Category[/td]
[/tr]
[tr]
[td]P1[/td]
[td]C1[/td]
[/tr]
[tr]
[td]P1[/td]
[td]C2[/td]
[/tr]
[tr]
[td]P2[/td]
[td]C2[/td]
[/tr]
[/table]

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

[table="width: 300, class: grid, align: left"]
[tr]
[td]Product[/td]
[td]Season[/td]
[td]Revenue[/td]
[/tr]
[tr]
[td]P1[/td]
[td]201606[/td]
[td]10[/td]
[/tr]
[tr]
[td]P1[/td]
[td]201607[/td]
[td]20[/td]
[/tr]
[tr]
[td]P2[/td]
[td]201606[/td]
[td]15[/td]
[/tr]
[tr]
[td]P2[/td]
[td]201607[/td]
[td]25[/td]
[/tr]
[/table]

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

[table="width: 300, class: grid, align: left"]
[tr]
[td]Category / Season[/td]
[td]06[/td]
[td]07[/td]
[td]2016[/td]
[/tr]
[tr]
[td]C[/td]
[td]25[/td]
[td]45[/td]
[td]70[/td]
[/tr]
[tr]
[td]1[/td]
[td]25[/td]
[td]45[/td]
[td]70[/td]
[/tr]
[tr]
[td]2[/td]
[td]10[/td]
[td]15[/td]
[td]25[/td]
[/tr]
[/table]

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.