Drill-Down Charts (Worksheets)
1. Determine what you would like your drill path to be.
Drill paths can be dates — like year, to quarter to week — or different levels of an organization — like region to store to department.
Different slices of the same data will be displayed at every point in the drill path. The axes change, but the value represented stays the same. Whatever drill path you want to set up, you need to make sure that you have all of the data you need at the lowest granularity you want to display. If you want to display sales data by department but you only have sales data by store, then you won’t be able to accurately drill down to the department level.
2. Create your drill-down path by adding multiple fields to the Axis.
The drill path will be top to bottom.
If you are creating a date-based drill path, you can use the column menu to create the path. Add in a date field that is at least as granular as the smallest unit you want to see. For example, if you have a [DATE] field that shows transaction timestamps that includes year-month-day hour-minute-second-millisecond, then you can use that field. If you want to show day, and your field is truncated to year, then that will not work.
Once you have the date field in place, you can go to the column menu and choose “Add Date Drill Down” and select the level of truncation you would like. If your [Date] column has more data than you’d like to show on the chart, don’t forget to add truncation to that field as well.
3. Add your Value field and apply an aggregation.
To make drill down work properly, the aggregation of your value field must occur in the chart.
Select the field with the most granular data values, most likely from your Base Columns. Next, choose how to aggregate your data.
To show the total sales for different points in time, you could use [Line Item Total] as your Value, then apply the Sum aggregation directly in the chart. This will show the Sum of the [Line Item Total] for every point in your drill path.
If you have aggregated data in your worksheet to the year level, if you use that field as the Value, you will not be able to see accurate data for Quarter, Month, or other smaller units of time. Instead, you want to perform the aggregation in the chart to get accurate data for every slice of the drill path.
4. Rename the fields in your chart.
If you would like to see different names for the levels of the drill path, or a different name for your value field, you can rename these fields in the chart. Simply double click on the name, or open the column menu and choose ‘Rename’.
5. Check your drill path by clicking through it.
You can navigate the drill down by using the menu in the upper left to change the granularity of the chart. You can also click into specific points on the graph to the next level of data for just that point.
The menu in the upper left adds context to where you are in the drill path.