This is one of my first few posts, being new to the community and Tableau itself.
I have a simple dataset with 5 fields: Product Type, Product, Year and Sales, Profit
I need a simple line graph showing %Profit (=sum(profit)/sum(sales) for each product) trend by year with products as color (legend).
Need to show Product Type as single select filter (Filter options: Type1, Type2 or All).
Also need to filter on a year range (simple slider filter i.e. Year From and To).
Finally need a filter to show Top 5 products by %Profit (only need to filter data and not show filter).
However, when I add this filter as follows, I only get 2 products when selecting Type1, 3 products when selecting Type2 and 5 when selecting All.
1. Add filter on Product Type
2. Create filter as Top 10
3. Select filter option as 'By field'
4. Filter configuration as Top 10 by %Profit (calculated field) with aggregation type as Custom (only Custom option is available when selecting the calculated field)
What I really need is to identify the top 10 products after the product type and year range filters are applied to the data and not vice versa.
In effect, I will have to show the top 10 products by % profit for any combination of year and product type filters.
How do I do this?
PS: I am somewhat familiar with the level of detail calculation concept. But just can't figure out what to apply in this case.