Have you ever found yourself searching for creative ways to save space in your Workbook? If so, you might want to learn how to use a list parameter to switch between dimensions displayed.
- Create a new “List” control element
a. Change the “Control type” to “Parameter”
b. Create a manual list with “Value type” set to “Text”
c. Enter the dimensions to switch between (I chose to set display values for clarity’s sake)
d. Re-name the Control ID (I chose to call it
- Create a Pivot Table with a new column in the “Value” section of the left-side panel; use the formula:
Switch([Dimension-Switch], "Count", CountDistinct([Order Number]), "Currency", Sum([Price] * [Quantity] - [Cost]), "Percent", PercentOfTotal(CountDistinct([Order Number]), "grand_total"))
- Add conditional formatting rules to change format depending on which parameter value is selected
a. Choose the column to apply the custom formatting to (I called my column
b. Set each “Formatting Rule” to “Custom formula”, using the formulas:
[Dimension-Switch] = "Count"
[Dimension-Switch] = "Currency"
[Dimension-Switch] = "Percent"
c. In each conditional formatting rule, select the Format you want to apply to the dimension
Hooray! Now you’ve got a concise Pivot Table to look at.