bratwoorst
New Member
- Joined
- Aug 10, 2016
- Messages
- 2
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
Next I have a table with revenues per product and season (time)
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
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?
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?