In order to create a drill down the data set needs to have hierarchical information .
Superstore will be used in this example to make a Tree map, using Sales on the Size mark.
To create a hierarchy drag the 2nd level dimension of the hierarchy onto the 1st dimension. In the Superstore data set Category is the 1st level and Sub-Category is the 2nd level hierarchy.
The hierarchy should look like this:
The Category dimension goes on the Details mark, Sales on the Size mark.
Clicking the small + on the right side of the Category pill adds the next hierarchy level dimension to the view.
Right click on the Category pill in the Data Pane and create a Set, the tick boxes can be left unchecked. Name it something appropriate.
Next, the newly created Category set needs to be connected to the Sub-Category dimension. This is done by creating a calculated field:
IF [Category DD Set] THEN [Sub-Category] ELSE “” END
Next, create a new set using the Calculated field from above.
Then create another calculated field that connects the Category set and the calculated field set (Sub Cat DD Set) to the Manufacturer dimension.
IF [Category DD Set] AND [Sub Cat DD Set] THEN [Manufacturer] ELSE “” END
Once this is done, replace the Category pill that’s on the Detail mark with the first calculated field, then replace the Sub-Category pill with the second calculated field.
Create a new Dashboard and drag the Tree Map sheet onto the view.
Select Actions from the Dashboard tab at the to of the screen. Click on Add Action and select Change Set Value.
Make sure the correct Sheet is selected, go to the Target Set section and select the Category Set, while making sure the “Remove all values from set” radio button is selected. Click OK and repeat the process, making a second set action selecting the Sub Category set.
The selected category will break into its Sub-Category while leaving the other Categories in the view.
Another calculation is needed to add the right labels when viewing the sub categories.
IF [Sub Cat – Manu DD Calc] !=”” THEN [Sub Cat – Manu DD Calc] ELSE
(IF [Category – Sub Cat DD Calc] !=”” THEN [Category – Sub Cat DD Calc] ELSE [Category] END)
END
The above calculation is a nested IF statement. Its basically saying IF the Sub-Category – Manu calculation doesn’t equal null the return the true value of the calculation which is the Manufacturer .
That is unless the set action is showing the Sub-Category in the view, in that case return the true value of that calculation which is the Sub-Category value unless the view is showing the Categories. In that case return the Category value. The Category and Sub-Category pills will need to be added onto the Tooltip mark for this to work.
Unfortunately as the Manufacturer measure is actually a Group it cannot be displayed in the Tooltip or anywhere else in the view without destroying the structure of the chart. In this case the drill down will only work down to the Sub-Category.
