Skip to content

Building a Chart with a Dynamic Measure

3 min read · Updated 13 Nov 2026

This recipe builds a chart whose measure viewers choose from a dropdown. The example switches between Total Sales Amount and Total Quantity. A conditional measure picks one of them from a parameter, a workbook variable sets the parameter, and an action sets that variable when the viewer changes the dropdown.

This setup switches between two measures. To let viewers choose from more measures or dimensions in several charts from one dropdown, without an action, see Drive alternate measures & dimensions using variables & dropdowns.

Create four measures:

  • Total Sales Amount, the total sales amount.

    New Measure dialog for Total Sales Amount, using Sum of the 1 Sales Salesamount field with format $#,##0.

  • Total Quantity, the total quantity sold.

    Update Measure dialog for Total Quantity, using Sum of the 1 Sales Orderquantity field with format #,##0.

  • measure switch, a measure that holds only a parameter, with a default value of 1. For how measure parameters work, see Measure Parameters.

    New Measure dialog with the Parameter type, value 1 and parameter name measureSwtich, named measure switch.

  • Chart measure, a conditional measure: if measure switch equals 1, then Total Sales Amount, else Total Quantity.

    New Conditional Measure dialog: if measure switch equals numeric 1, then Total Sales Amount, else Total Quantity, named Chart measure.

  1. Create a number variable named measureSwitch with a default value of 1. For how to create variables, see Creating variables to use with objects.

    Variable settings for measureSwitch, of type Number with a default value of 1.

  2. Create a string variable named selected measure with a default value of Sales Amount.

  3. Create a chart with the dimension you want and the Chart measure measure. In the measure’s properties, link the measureSwitch variable to the measure’s parameter under the defined parameters, and set the custom label to the selected measure variable, so the chart names the measure it shows.

    Chart measure properties with the custom label set to the selected measure variable and measureSwitch linked as a defined parameter, both highlighted.

  4. Add a dropdown control to the sheet from the controls in the left panel.

    Controls panel in the left sidebar with the dropdown control highlighted in red.

  5. In the dropdown’s properties, select the selected measure variable and add two custom options: Sales Amount and Total QTY.

    Dropdown input properties with the selected measure variable and custom options Sales Amount and Total QTY highlighted.

  6. Place the dropdown next to the chart title or wherever it reads naturally, and group the chart and the dropdown together.

    Bar chart titled Countries Performance by, with a dropdown set to Sales Amount beside the title, showing sales by country.

The action translates the viewer’s choice in the dropdown into the number the conditional measure reads.

  1. Open the Actions dialog.

  2. Drag the Variable change event to the canvas and select the selected measure variable.

    Actions editor block labelled Variable change with the selected measure variable chosen.

  3. From the Conditions category, add an if-else block inside the Variable change block.

    Empty If..Else action block inside a Variable change block for the “selected measure” variable.

  4. Set the condition to selected measure = Sales Amount. In the branch that runs when the condition is true, set measureSwitch to 1. In the else branch, set measureSwitch to 2.

    If selected measure value = Sales Amount, set measureSwitch to 1, else set measureSwitch to 2, in a Variable change block.

Select Preview in the top bar and pick each option in the dropdown. With Sales Amount selected, the chart shows Total Sales Amount; with Total QTY selected, measureSwitch becomes 2 and the chart shows Total Quantity. The measure label follows the option you picked.

  • As built here, the recipe switches between two measures, because the conditional measure and the if-else block each have two branches.
  • measureSwitch is a workbook variable, so every chart that uses Chart measure with that variable linked changes when a viewer picks another option.