You need to set the context whenever you need Top N data. You have to set it for all the dimensions except the one in which you apply the Top N filter. This will ensure that you get the right output within the filtered data (context).
1. To set the context, right click on the dimension in the filter and add to context. You have to add Project Status, Savings Lever and Savings Type to context.
2. After adding the dimensions to the context, you may still feel that you are not getting Top N as required.
In the the below data, it seems as if the total records shown are 16 based on the serial number. But, in reality the output is correct because you have 10 distinct projects. Since you have the SBG dimension, the Project is broken out further to increase the number of records.
3. You should set the table calculation for the Serial Number so that the count is displayed correctly as shown below. Right click on the Sl No table calculation and click on "Edit Table Calculation".
The updated workbook is attached.
test file.twbx 405.6 KB
Hi Hari Ankem,
Thank You So Much, its working exactly.
I have one more Question,
Is it possible to use one more parameter for the same sheet..? Which is already there in workbook "Top N Spend", it has to work same as "Top N Annualized spend" but it should filter based on "Spend" instead of Annualized Spend.
Thanks & Regards
To be able to get the top N for Spend or Annualized spend, you should create a parameter that lets you identify which metric you need. Then use that parameter to create a calculated field that gets you the selected metric value.
1. Parameter creation:
2. Calculated Field to assign the selected metric value based on the parameter selection.
3. Use the above calculated field to identify which Top N you need.
Here are your results now:
1. Top 15 by Annualized Spend
2. Top 15 by Spend
You can change your N value as required. Hope this helps.
test file.twbx 407.6 KB
Hi Hari Ankem,
Thank You So Much for your support. It's working. So happy to see the solution with the step by step.
You are welcome.