maximus_dongus
New Member
- Joined
- Jul 21, 2014
- Messages
- 5
Hello, I have a powerpivot with a fairly primitive data structure:
<tbody>
</tbody>
I need to create rating of top 20 most popular products (by week)
Column "Weekly Rating" must have values like: +1 or -1, depending whether the product's place in week's report has changed regarding the previous period.
So here's the question:
How do I determine which product within given week (same values in "Week_number" column) has taken which place? The data will be appended weekly automatically, so the formula must be able to automatically calculate weekly data.
Here's the example with relevant data structure and dummy values to represent approximate table format
https://drive.google.com/file/d/0B-vipi2MSEVUWENnM0N3My1HLUk/edit?usp=sharing
P.S.: originally, all data is pulled by PowerPivot query from company OLAP cube.
Week_number | Product_Category | Sales_num | Product |
1 | Product Category 5 | 0 | Product 1 |
1 | Product Category 3 | 24 | Product 4 |
2 | Product Category 1 | 5 | Product 5 |
etc | etc | etc | etc |
<tbody>
</tbody>
I need to create rating of top 20 most popular products (by week)
Column "Weekly Rating" must have values like: +1 or -1, depending whether the product's place in week's report has changed regarding the previous period.
So here's the question:
How do I determine which product within given week (same values in "Week_number" column) has taken which place? The data will be appended weekly automatically, so the formula must be able to automatically calculate weekly data.
Here's the example with relevant data structure and dummy values to represent approximate table format
https://drive.google.com/file/d/0B-vipi2MSEVUWENnM0N3My1HLUk/edit?usp=sharing
P.S.: originally, all data is pulled by PowerPivot query from company OLAP cube.