Show only Top 10 Total in Cross Table using Show/Hide

[Alexis Isenberg]

When working in a Cross Table, if you use the Show/Hide Items option it will filter down the data to what you've selected, but the GRAND TOTAL, is still the total of all the data. How can you get the total of just the Show/Hide items you've chosen.

For example,

If I've chosen to display the top 10 accounts by units and I want to know the total of those top 10 accountsand see the total of all the other accounts (not including the top10)how can I display that

If not available in a cross table, what other visualizations will allow me to see this information

Do you need to create a custom express or calculated column

[Sean Riley]

If you use visualization properties > Data > Limit data using expression to limit your data then that will exclude data from the entire visualization, including the totals.

Your "Limit data using expression" expression would just need to mirror the aggregation/grouping you have in your cross table. Here are a few examples to give you an idea of the functions/syntax:

 

Rank([sales],'desc')

[Alexis Isenberg]

does this respect the filters and filtering schemes

[Sean Riley]

Yes, this is effected by filtering.

[Sean Riley]

Yes, this is effected by filtering.

[Peter Ferrett]

I know this is an old question, but in case anyone else comes across this post lookingfor an answer: In the "Totals" section, change "Calculate Totals" option to "As the sum of cell values". The total will now display only the sum of the items restriction by the Show/Hide Items.