The first step is to step up a drill down dimension. Say product and sub-product called say 'Product Drill Down'
Then you use this 'Product Drill Down' dimension in the chart
So when you click (filter by) on one product and one product only the chart will automatically drill down to the sub product level for that chart
To set up the Drill down dimension. Just click on the field you want (2 or more) and then name and click add dimension
and the above is the automatic drill down way to do this
Another way is to use alternatives (but not available for a table. Maybe use a Pivot Table) The enables a drill around feature. I prefer alternatives (when Im doing a chart for myself) as I'm in control. I don't have to follow the preset drill down
For example if I drill by
with alternative I can drill by Products to Customers if I want to. Or view all sub-products or customers
But if I'm setting a chart up for other users I often pre-set the sequence.
Thank you very much for the help.
The chart has three fields: product type, sum([totalsales]) and count(distinct total [product]).
Basically we want below result.
1. At product level, we have product type, sum([totalsales]) and count(distinct total [product])
2. Drill down to sub product level, we have product type, sum([totalsales]) and count(distinct total [sub product])
Is there anyway to achieve this?
Not as you have stated above. Unless it can be done using an extension (although what sort of chart do you want).
One option to do this is just have one dimension (Product Type) and three measures. For example use a combo chart (or a table is another option). With a bar as sum([totalsales]) and maybe use lines as count(distinct total [product]) and count(distinct total [sub product]) (using maybe the secondary axis for the counts).
I would also have a drill down from Product Type to SubProduct. Sort of like this (its one example I have in my demo) but with three measures and drill from Product Type to sub product