Emulating rating in PowerPivot

[maximus_dongus]

Hello, I have a powerpivot with a fairly primitive data structure:

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.

 

[scottsen]

Sounds like you will want to look into the RANKX function.

[Lifetime Rank] := RANKX(ALL(Sales[Product]), [Total Sales])

Depending on what else you got going on, you will want to wrap that in a CALCULATE to constrain to 1 weeks sales.

 

[maximus_dongus]

Depending on what else you got going on, you will want to wrap that in a CALCULATE to constrain to 1 weeks sales.

That's the culprit for me... I haven't the slightest idea how to constrain it to 1 week.

 

[scottsen]

Best-guess (eg, untested )

=RANKX(FILTER(ALL(Sales[Product]), Sales[Week_Number] = MAX(Sales[Week_Number])), [Total Sales])

As long as you have Week_Number on rows, I think that will work...

 

[maximus_dongus]

Best-guess (eg, untested )

=RANKX(FILTER(ALL(Sales[Product]), Sales[Week_Number] = MAX(Sales[Week_Number])), [Total Sales])

As long as you have Week_Number on rows, I think that will work...

Hmm, this just sets 1 as rank of every row...

 

[maximus_dongus]

No clue how to edit posts here, so do pardon my double post.

=RANKX(FILTER(ALL(Sales[Product]), Sales[Week_Number] = MAX(Sales[Week_Number])), [Total Sales])

Correction: It seems that following the logic in this formula, rankx is only applied to the rows where week_number is the highest (e.g.: the last added week number), which is convinient for updating the table but not so much when the data in the entire table is refreshed weekly via OLAP query.

The problem with this exact task is that total number of rows added weekly is different, so this makes me wonder
is it possible to limit RANKX to assign no more than numbers 1-20 to the top-selling items (if there are ties - assign rank in alphabetical order by the Product column) ?
Also, is it possible for RANKX to assign different ranks to different product category (product_category column), i.e. different rating within different categories ?
On top of all, the issue of filtering weekly results remains... how do I do it with the data being added dynamically ?

 

[scottsen]

Total Sales:=SUM('Таблица1'[Sales_num])
Ranky:=RANKX(FILTER(ALL('Таблица1'),'Таблица1'[Week_Number] = MAX('Таблица1'[Week_Number])), [Total Sales])

This worked fine for me, *IF* I put both Week_Number and Product on rows.

 

[maximus_dongus]

This worked fine for me, *IF* I put both Week_Number and Product on rows.

I'm sorry, I'm really confused by this statement. Do you mean that you have restructured the table format? Does it differ from original structure?

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>

If so, could you, please, demonstrate?

Thanks for your time, I really appreciate the help.