This blog is a step by step guide of single drill down – one of the tricks I have learned from Bethany Lyons’s work. I will demonstrate this flexible way to drill down to the next level of detail without expanding the entire hierarchy or having an extra sheet.
I used the US superstore sample data and drilled down to see AVG Sales value from Region to State and to City level.
1. Create Region Set
2. Set up a Set Action – Update Region Set
3. Create a Calculated Field for State level drill down, and drag it on to Rows
Here we have the first level of drill down.
4. Create States Set using the States calculation (Not the State filed from the data source)
5. Set up a Set Action – Update State Set
6. Create a Calculated Field for City level drill down, and drag it on to Rows
From here, your two levels of single drill down should be working!
Now the only thing to do is formatting, then you will get the sheet in the demo:
7. Put computed sort on every dimension
8. Hide Filed Labels for Rows
9. Add AVG(Sales) to Color and Label Mark card
That’s it, I hope you have a single drill down sheet ready to go. If you have any comments or would like to chat, you can find me on LinkedIn!