Sorry, one more thing I thought of. I also need the year added as a column as updated in the attached workbook and that throws another wrench into what I'm looking for because I'm looking for the Top N based on the year. It appears it's giving me the Top N based on total in the data and not the particular year I'm choosing.
1 of 1 people found this helpful
If you are looking to view the Top N for a specific company and year you can create a calculated field based off an index:
INDEX () <= Top 10 and place it on the filter shelf running along the Client dimension.
I attached a workbook. Is this what you are looking for?
Thanks for the workbook. It's close, but not quite there because when indexing it looks like I can't get Top N for all companies when all companies are selected. Plus when I add a total to the column, it's giving me more than what the Top N sum to.
Try this-- I've created one field that you can use as your filter:
You need to do an advanced table calc and address both Company and Client, and restart every company:
I don't think you're going to get what you're looking for if you allow a user to look at multiple years of data at once (you can't sort data by two years at once). So, I've set your year filter to "single value dropdown." My work is on the "Year Included Top 10 2015" Tab. Let me know if this is what you're looking for.
I forgot to actually sort the data. I've created another field to do this:
And used the same advanced table calc as above.
Top Data - BP.twbx 1.1 MB
Thanks Ben, but still not quite what I'm looking for. I'm looking to be able to get the Top N of both companies when both companies are selected. Then get the Top N of each individual company when only 1 of the companies is selected. Also, when I add a column total it's giving me the total for the company and not just the top N being shown in the window.
Ben, I forgot to mention about the years. I don't want to sort on both years, I just want to get the top N for 2015 while showing their historical performance at the same time. Then if needed, I'd like them to be able to get the top N for 2014 to see how they did in 2015. I'm not asking for to much am I?