Example of combining multiple numeric columns in a model for PowerMetrics

Have you discovered how PowerMetrics can give you insight into your data?  This Community post takes you through the process of modelling a small data source, and building an advanced PowerMetric that allows you to track three different metrics over time.

To build a PowerMetric you will need to first model the underlying data source.  You can do this from the About this Data source page by selecting the Create modelled data source button.  Immediately, you are presented with the same structure as your underlying data source.

When you model a data source, you want to include only the information you want to visualize.  You also have to consider how a PowerMetric is built.

A PowerMetric will typically have:

  • a date column (or you can select the date and time during the import process), 
  • a metric that you want to track (in this case we are going to track three metrics, ie downloads, video plays and live stream),
  • and up to five segments that will let you visualize your data in many different ways. Segments are typically formatted as text in your model.

In our example, we will need to use formulas to consolidate the dates, segments and metrics into their own columns. This will then allow us to reference these columns when building our PowerMetric.

For our Date column, use the REPEAT function to repeat the dates for each of our metric types columns (ie downloads, video plays, and live streams).  The SLICE function removes our column header. In the Properties panel, we suggest turning off Automatically set format for the Data Format. You should also click Go to Properties to select the Exclude data before row option.

For our Tutorial column, we will use the same formula to repeat our tutorial names for each of our metric types.

For our Metrics column, we will use the ARRAY function to join the download, video plays and live stream metric values into one column.

For the Metric Types column, we will use the ARRAY function and the REPEAT function to repeat the column header names (downloads, video plays and live streams) for the number of items we have in the tutorial column.

By doing this, it has transposed the data into four separate columns, the columns we need to build our PowerMetric.  Did you know, a PowerMetric stores historical data? This means when new data is added to our underlying data source, our modeled data source will be updated, and added to our PowerMetric.

To build a PowerMetric, you start from the About this Modelled Data source page and select the Create a PowerMetric button.

You will then be prompted to make a number of selections.

As we started with the modelled data source, the right data source is highlighted at the Connect to your data screen.

At the Select a metric value screen, select Metrics as this is the measure we want to sum over time.

At the Segment metric value by category screen, select Tutorial and Metric Types.  

At the Select a date column, select the Date column.

Important: You will also need to confirm here how your data is aggregating over time.  For our example, we will select All values, as we want to include every value for every date in our data source.  Use this link to learn more about the different aggregation types.

In the last Options screen, give your PowerMetric a unique name.

You now have a PowerMetric that you can configure to display the data in a number of different component types. 
You also have the option to filter the data by selecting from a number of different preset date ranges.  

Check out our Master PowerMetrics YouTube videos to learn more about PowerMetrics.


Please sign in to leave a comment.