I have this crosstab displaying top 20 selling products using topCount([Product];20;[Sales]), sorted by Sales descending:
Sales Calculated1 Calculated2
Product1 Country1 100
Country2 200
Total by Country 300
Product2 Country1 90
Country2 50
Total by Country 140
I would like to achieve something like this:
Sales Calculated1 Calculated2
1 Product1 Country1 100
Country2 200
Total by Country 300
2 Product2 Country1 90
Country2 50
Total by Country 140
... etc up to
20 Productx Country1 2
Country2 2
Total by Country 4
however when I add rownumber i get something like this
Sales Calculated1 Calculated2
1 Product1 Country1 100
Country2 200
Total by Country 300
3 Product2 Country1 90
Country2 50
Total by Country 140
5 Product2 Country1 80
Country2 50
Total by Country 130
Any ideas?
RowNumber() returns the absolute row number of the table, so the second table is correct. In order to get what you want, try the following.
1. Create a data item with the expression:
total(1 within set periodsToDate([cube].[dimension].[hierarchy].[all level];currentMember([cube].[dimension].[hierarchy])))
rank([Sales] within set [Products])
Next, unlock the report (by clicking on the padlock) and drag that data item to the left of Product inside the product node. It should now look something like:
<Data Item1><#Product#>
Separate them by dragging a text item between them with a space.