How to create a calculated table of top values in Microsoft Power BI

Calculated tables are helpful tools when you want to add calculated values to the model instead of calculating them on the fly.

It’s not unusual for Microsoft Power BI designers to look beyond the natural data for insight. When this happens, they have many tools at their disposal: filters, measures, calculated columns and even calculated tables. The latter allows you to perform complex calculations and store the results as part of the model, instead of performing calculations on the fly. The values are in memory and ready to go when needed.

In this tutorial, we’ll create a table based on existing data to extract the top five sales. You can then use that calculated table in visualizations. There are many scenarios where you might want to do so. Later, when building visualizations for that specific purpose, you can use the calculated table instead of the original data that would otherwise require a measure and perhaps a filter to evaluate the same values.

I’m using Power BI Desktop on a Windows 10 64-bit system, but you can also use Power BI Service. To follow along. Download the demonstration .pbix file, and then, double-click to open it in Power BI.

SEE: Hiring Kit: Database engineer (TechRepublic Premium)

What a calculated table is in Power BI

Technically, a calculated table in Power BI is an in-memory table created by a Data Analysis Expressions, or DAX, calculation. Calculated tables load into memory, which means they’re always ready. This has benefits, including:

• Calling in-memory data is more efficient than retrieving the same values from an external data source or recalculating them in real time.
• You won’t have to retrieve the data again later.
• The values are always current.

On the downside, calculated tables will increase the size of your .pbix file, but it’s usually a fair trade-off. For now, you might think of a calculated table as a permanently filtered record set. It’s more than that, but that description should clarify its purpose and many benefits.

Now, let’s put all this information to use to create a top five sales table.

How to create a calculated table in Power BI

A list of the top five or so customers is important to most organizations because those customers are likely to represent the largest percentage of all sales. To create a calculated table that returns only the top five customers in the demonstration .pbix file requires two things:

1. A table of customers with their total sales
2. A way to filter those to only the top five

Fortunately, DAX handles all of this with the TOPN function (Listing A).

Listing A

Top 5 Sales = 

    TOPN(5,Sales,[Amount],0)

Figure A shows the resulting table based on the Sales table and the DAX TOPN function used to create and populate this table.

Figure A

In this case, you don’t even need the CALCULATETABLE function because the TOPN function returns a table. The code won’t always be this simple, but in this case, everything needed is in the same table: Sales.

Here’s how it works:

• The first argument, 5, restricts the new table to only five records.
• The second argument, Sales, denotes the table with the values we’re evaluating.
• The third argument, Amount, denotes the column by which the function sorts the data.
• The fourth argument, 0, specifies a descending sort.

There’s the top five records. The function creates a new table named Top 5 Sales and populates it with the data in the Sales table, but copies only the first five records in the sorted dataset.

To create the table, do the following:

1. Click the Table Tools tab.
2. Click New Table in the Calculations group.
3. Enter the DAX function into the formula bar, and then, press Enter or click the checkmark to the left.

Once you have the table in place, you can build visualizations on it. As is, it will always display the top five sales from the Sales table, unless you add a filter.

How to use the calculated table in Power BI

You’ll base a visualization on the calculated table, as you would any other. Figure B shows a pie chart that exposes a bit of keen insight — all five top sales are close in value. To build this, click the Pie Chart visualization in the Visualization pane. Then, drag SalesDate to the Legend bucket and Amount to the Values bucket.

Figure B

Once you have this visualization, you can add filters. Figure C shows a region filter that currently displays only the Central region top sales. To add this filter, open the Filters pane and drag the Region field from the Top 5 Sales table to the Add Data Fields Here bucket.

Figure C

To apply a filter, simply check one of the regions. The pie chart updates accordingly by displaying only the values for that region. You already know that the values represent the top five. Now, you can easily see those sales by region.

Creating a calculated table is one of many ways to report calculated values based on the natural data. You can reference related tables, specify column data types and formatting, name the table and the columns whatever you like, and base effective visualizations on them.