How to create dynamic measures in Excel with Python in Excel

One of the most exciting skills to learn in BI tools like Power BI or Tableau is how to create dynamic measures. The idea is that since dashboard space is limited, using dynamic controls—such as allowing users to select which measure to display—keeps the dashboard both compact and powerful for users.

In this post, let’s dive into how to create dynamic measures in Excel, using Python to make it happen. We’ll work with a well-known fuel economy dataset, allowing users to choose both the measure they want to aggregate and the type of aggregation.

If you’re unfamiliar with creating dynamic dropdowns using tables and dynamic arrays, I recommend checking out this post first:

Follow along with this post by downloading the exercise file below:

The first step is to create a helper worksheet containing, to start, a table that lists potential aggregations the user might request for grouping the data. The first column will display the options visible to the user, while the second column will specify the corresponding Pandas method used to perform each aggregation.

Go ahead and paste this table into a helper worksheet to get started:

Next, in a front-end worksheet, prompt the user to select the desired aggregation type. Set up a dynamic dropdown menu linked to the Operation column of the helper table. If you’re unsure how to create a dynamic dropdown, refer to the post mentioned earlier for step-by-step guidance.

The next step, as you might have guessed, involves performing a lookup in the helper worksheet. This will retrieve the corresponding Pandas method based on the aggregation type selected by the user in the front-end worksheet:

=XLOOKUP('dynamic measures'!B1, agg_methods[Operation], agg_methods[Method])

Next, assuming the mpg dataset has been read into Python in Excel as mpg_df, we will run the following Python code to generate a list of all the quantitative columns in the dataset. This list will serve as the basis for the next dynamic dropdown, allowing users to select the measure they want to aggregate by. If you’re not satisfied with this list, you can always modify the data type of the relevant column in Pandas to include or exclude it!

mpg_dimensions = mpg_df.select_dtypes(include=['number']).columns

This is coming together nicely! Now, set up a corresponding dynamic dropdown in the front-end worksheet, based on this dynamic list of measures. With this setup, the user will be able to select both the measure and the aggregation type, and instantly see the results returned.

As you can see, I’m using a helper worksheet to handle some intermediate staging tasks—a common practice when building reports and models in Excel. One more thing I know I’ll want to include in the front-end is a chart. To set this up, I’ll quickly create a dynamic label for the chart here in the helper worksheet using CONCAT(). This label will display the selected measure and aggregation type.

=CONCAT('dynamic measures'!B1, " of ", 'dynamic measures'!B2, " by origin")

For simplicity, I’ll always use origin as the dimension to group the data by. However, if we wanted to make this dynamic, it would be straightforward to do by following the steps I’m about to demonstrate.

Alright, we’re getting close! The next step is to create the results in Pandas using the groupby method. Here’s how it works.

We start by storing the user’s selections for measure and aggregation in mpg_selected_measure and mpg_selected_agg, respectively.

The code uses getattr to dynamically call a Pandas method. First, mpg_df.groupby('origin')[mpg_selected_agg] groups the dataset mpg_df by the origin column and selects the column specified by mpg_selected_agg.

Then, getattr dynamically applies the method specified by mpg_selected_measure (e.g., mean() or sum()), allowing the operation to adjust based on the user’s input from Excel. Finally, the resulting data is sorted in descending order with .sort_values(ascending=False). This sorting step helps ensure that the resulting bar chart is displayed in a more organized and readable way.

# Data inputs
mpg_selected_measure = xl("helper!B13")
mpg_selected_agg = xl("'dynamic measures'!B2")

# Group data by selected measure/agg, sort results descending
getattr(mpg_df.groupby('origin')[mpg_selected_agg], mpg_selected_measure)().sort_values(ascending=False)

Finally, I’ll select the data source and insert a bar chart. While Python in Excel can generate bar charts, I find that Excel’s native bar charts work perfectly fine for this purpose. They’re more familiar to Excel users and offer greater interactivity, allowing users to use basic tooltips and easily select or edit the data source to better understand the chart. The end result will be a fully dynamic, measure-driven visualization—tailored to the user’s inputs.

In this post, we looked at creating dynamic measures in Excel using Python. Dynamic measures — and dimensions — can be powerful tools for a variety of applications within Excel.

For instance, you can apply this technique to financial modeling, where users can switch between different key performance indicators (KPIs) like revenue, profit, and expenses to see how they perform across various dimensions, such as time periods or geographic regions. It can also be used in sales analysis, where dynamic controls let users compare metrics like units sold, sales growth, and conversion rates across different product categories.

If you’re inspired to try dynamic measures for new use cases in Excel or have questions about the process, please let me know in the comments.

The post How to create dynamic measures in Excel with Python in Excel first appeared on Stringfest Analytics.