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.
Getting started…
- 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[Dimension-Switch]) - 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[Dimension])
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
Final Result…
Hooray! Now you’ve got a concise Pivot Table to look at.
I use it all the time to let users decide what’s important to them!