Requirement: Display Ranks across Columns in a Crosstab and the Total column needs to include only the Top 2 ranks along with Overall Total column for all Ranks.
Solution:
Step 1: Create the base query Query 1 with the following data items: Product, Year, Sales, Rnk, Top 2 Sales Total.
Rnk - Rank([Sales] for [Product])
Top 3 Sales Total - case when [Rnk] < 3 then [Sales] end
Step 2: Create the outer query Query 2 by referencing the base query Query 1. Drag the data items - Product, Year, Sales, Rnk, Top 2 Sales Total from the referenced query.
Step 3: Set the aggregate function of Rnk to None.
Step 4: Create the crosstab by dragging in the data items Product, Sales, Top 2 Sales Total from Query 2.
Step 5: Create the Overall Total column by clicking on the Summary button for Rank column.
Posts
2 comments:
Zephyr - Firstly, I really Appreciate the time put in by you to resolve my query. Thanks !
However, the above solution doesn't solve my problem. The case statement used by you doesn't have a default else clause. And the dummy data u have used has only 3 ranks so the total would come as top- 3 summary itself.
My question is suppose you had ranks 4 and 5 as well and the values would have been Rank 4-(1000,2000,3000)and Rank 5-(2000,3000,4000) for products A,B,C respectively.
I need two columns -
i) A top 3 summary(same numbers as shown by you) along with the
ii) Overall total which shall the following numbers - (59400, 14075, 41500) for A,B,C respectively ??
I have now updated the article to include the scenario mentioned and have included more data to present the same. Let me know if this helps.
Post a Comment