1. Create a set on your field. I've used superstore's to create a set of Top 10 sub-category on sales.
2. Now using the created set in a calculated field to group the Other sub-categories.
3. Then drag the fields like that. I've used set in columns shelf to get the others at right side.
To get the grand total go to Analysis menu tab>Totals>Show row grand totals
4. to get the waterfall chart first sort the chart in asc order and add table calculation on your measure (running total). Change the chart type to gantt bar from marks card. I've put Profit on color.
Let us know If this help.
Thanks for your reply
I have been able to do what you have done in the screenshot. But if you see, that is not giving the clear picture.
Bar for total in your screenshot is reaching at 25MM, however that should be around 15MM, and at the same level of Others.
What I want for the Bar of Total is, that should start from 0 and reach till the spend it covers (till Others). Because top N + Others = Total.
Hope I am making my query clear.
You've mentioned you wants a waterfall chart at the end. To get waterfall chart we've to use running total table calculation. Just because of the we're getting such total. If you don't want that then clear the table calculation from the measure. Then you can see the exact total (Top 10 Sub-cat + Others). Let us know If you required any further assistance.