How to use a parameter to switch dimensions displayed in a Pivot Table

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…

  1. 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])
  2. 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"))
  3. 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:

Final Result…

Hooray! Now you’ve got a concise Pivot Table to look at.
Dimension Switch


Love this! :clap: :heart: I use it all the time to let users decide what’s important to them!


This great! I just figured out how to do this on my own last week to change the color category dimension in visualizations - glad to see I didn’t make it more complicated than necessary :nerd_face:

1 Like

Fantastic. Thank you for the articles.

1 Like