Here's what we'll need (Remember, I'm using Excel 2013, NOT 2010 with the Add-In):
Data in the Data Model
Slicers (Connected to the Pivot Table)
Cubeset(Connection, Set Expression,[Caption],[Sort Order], [Sort By])
IfError(Value, Value if Error)
Data: I'm using World population Data found at: http://www.census.gov/population/international/data/worldpop/table_population.php
I added a Decade column to assist me in quickly grouping the data by decade.
Now, I'm adding this table to the data model simply by:
- Highlight the dataset
- Click the PowerPivot Tab at the top
- In the Ribbon, click "Add to Data Model"
- Home Tab
- PivotTable > Pivot Table
- New WorkSheet > OK
- Rows = Decade
- Values = Average of Annual Growth Rate (%)
- Slicer (Found in the Filters Group)
- Right-Click the Slicer
- Report Connections
- Check the Pivot Table you want the slicer to control
- Click OK
- Click somewhere on your Pivot Table
- Under "PIVOTTABLE TOOLS" On the Ribbon, click "Pivot Chart"
- Let's choose a Clustered Column Chart
- Click OK
CUBERANKEDMEMBER(connection, set_expression, rank, [caption])
IFERROR(Value, Value if Error)
- =CUBESET("ThisWorkbookDataModel",Slicer_Decade, "Decade")
- =IFERROR(CUBERANKEDMEMBER("ThisWorkbookDataModel",$A$1,ROW(Sheet1!A1)),"") - Remember, "Sheet1!" is the name of whatever sheet you're working in
- If no slicers are selected, an error would be returned, so "IFERROR" returns a blank value.
- Otherwise, if all slicers are selected (no filter) the value will be "ALL"
- Otherwise, if one or more slicers are selected, the first value in the list of slicers will be returned
- Validate that your formula in cell A2 has the dollar signs around A1 ($A$1).
- Copy the formula down to cell A10 (or Auto-Fill).
- I typically use the "All Borders" Border around the range to indicate that the cells are being used even if they are blank.
- =A2&IF(A3="","",", "&A3)&IF(A4="","",", "&A4)&IF(A5="","",", "&A5)&IF(A6="","",", "&A6)&IF(A7="","",", "&A7)&IF(A8="","",", "&A8)&IF(A9="","",", "&A9)&IF(A10="","",", "&A10)
In cell A14 copy/paste the following formula:
- =A12&TRIM($A$1)&": "&TRIM($A$13)
="Avg World Population by Decade "&char(10)&A14
*Char(10) will add the carriage return we need
Click on the chart title and in the formula bar type "=ControlSheet!$A$15"
You will see the this:
As you change the filters in the slicer, the title will change with it!
For the longest time I thought "Let's put the filters in the Title" was the best idea EVER!.. However, once I did it, I realized it wasn't a great Idea. If you have multiple slicers you've incorporated into your title it becomes WAY oversized and unruly. You see, when you link the title to the cell, ALL of the text is the same font size - you can't make the filter text smaller than the title text. For me, the title should be the focus and the filters just there if you need them.
Let's solve this: ADD A HORIZONTAL AXIS LABEL! - Since the data in this case is very self explanatory we don't need the axis label to describe the axis.
- Click on chart
- On Ribbon, Click "Design" in PivotChart Tools
- Click Add Chart Element
- Axis Titles > Primary Horizontal
- Click on the Axis Label on the chart
- Paste "=A12&TRIM($A$1)&": "&TRIM($A$13)" into the formula Bar
- Change the font size to "8"
- Change your Chart Title by simply typing the title in "Avg World Population Growth Rate (%) By Decade"
I hope you all enjoy this! It's been a royal pain trying to work without this solution, so if you share it, please share some of the credit with me if you reference this.