How to Create a Virtual Table in Power BI

Creating complex reports in Power BI often means going beyond simple drag-and-drop actions. When you need to perform calculations on a subset of your data that doesn't pre-exist as a table, you need virtual tables. This guide will walk you through what virtual tables are, why they are so powerful, and how to create them step-by-step using DAX.

What is a Virtual Table in Power BI?

A virtual table is a table that you define inside a DAX expression, it’s not stored physically in your data model. Think of it as a temporary, in-memory table that Power BI creates on the fly to perform an intermediate calculation. Unlike the tables you import from a source like Excel or SQL Server, a virtual table only exists for the duration of a single DAX measure's calculation.

Imagine you're trying to solve a complex math problem on a whiteboard. You might scribble down some small, intermediate calculations on the side to help you work towards the final answer. A virtual table is like that temporary calculation on the side of the whiteboard. It helps you break a big problem into smaller, more manageable pieces, but you erase it once you have the final answer.

Why Bother Using Virtual Tables?

Virtual tables are a gateway to more sophisticated data analysis in Power BI. They aren't just for show, they solve very specific and common reporting challenges. Here’s when they become incredibly useful.

For Complex, Multi-Step Calculations

Many business questions can't be answered with a single calculation. You often need to filter a table, perform an aggregation on that filtered subset, and then perform another calculation on the result. Virtual tables are perfect for this.

For example, you might want to calculate the average sale size but only for customers who made more than five purchases. You'd first need to create a temporary (virtual) table of those specific customers, and then you would calculate the average against that isolated group.

To Supercharge Your Filtering Logic

The CALCULATE function is arguably the most important function in DAX, and virtual tables enable you to use it in incredibly dynamic ways. Instead of using a simple filter like 'Product'[Color] = "Blue", you can pass a whole table as a filter argument. This allows you to apply an entire set of complex conditions generated from a virtual table, giving you precise control over your calculations.

For Dynamic Segmentation and Analysis

Virtual tables allow you to segment your data on the fly. You can create lists of "Top 10 Products by Profit," "Customers with Repeat Purchases in the Last 30 Days," or "Underperforming Sales Regions" within a measure. Once you have this virtual segment, you can perform any calculation you want on it, such as counting the members, summing their sales, or averaging their performance metrics.

Key DAX Functions for Creating Virtual Tables

Virtual tables are built using DAX functions that return a table instead of a single value. Mastering these functions is the key to unlocking their potential. Here are some of the most common ones:

• FILTER('TableName', [FilterExpression]): Scans a physical table and returns a new (virtual) table containing only the rows that meet the criteria you specify in the filter expression.
• SUMMARIZE('TableName', [ColumnToGroupBy], "NewColumnName", [Expression]): One of the most common ways to create a virtual table. It groups your data by the columns you specify and calculates aggregated values for each group, like summing sales by customer.
• ADDCOLUMNS(<table>, "NewColumnName", [Expression]): Takes any table (physical or virtual) and adds one or more new columns to it. This is great for adding intermediate calculations to your virtual table before the final step.
• TOPN(<n_value>, <table>, [OrderByExpression], [Order]): A powerful function that returns the top 'N' rows of a table based on an expression. Perfect for "Top 10" style reports.
• CALCULATETABLE(<table>, [FilterExpression]): The table-generating cousin of CALCULATE. It returns a table evaluated in a modified filter context.
• ALL(), ALLEXCEPT(), VALUES(): These functions are crucial for manipulating filter context and often used as the starting point inside other table functions to create tables of all values or distinct values in a column.

Step-by-Step Guide: Calculate Average Revenue of Top Customers

Theory is great, but let’s make this practical. Let’s say we want to answer a common business question: "What is the average total revenue from our top 100 customers?"

We can't just use AVERAGE(Sales[Revenue]) because that would give us the average revenue per transaction for all customers, not the average of the total revenue for our best buyers. This requires multiple steps, making it a perfect use case for a virtual table.

• A list of all their customers and each customer's total revenue.
• From that list, identify the top 100 customers based on their total revenue.
• Finally, give an average of the total revenue for that top group.

Step 1: Write a DAX Measure (not a New Table)

First, remember that a virtual table is created as part of a measure. You don’t do this in Power Query or by clicking "New Table." You create a "New Measure" and build the logic there. We will also heavily use variables (VAR) because they make complex DAX clean, readable, and more efficient.

Step 2: Build Virtual Table to Summarize Sales by the Customer

We need to generate a temporary table of every customer and their lifetime revenue. We can't identify the top customers until we know the lifetime revenue for everyone. We use the SUMMARIZE function to roll up all transactions by a customer and get the sum of those sales transactions. Below is a measure in a variable, vCustomerSales, where we are able to sum all sales transactions by each individual customer.

Average Revenue of Top 100 Customers = 
VAR vCustomerSales =
    SUMMARIZE(
        'Sales',
        'Customer'[Customer ID],
        "TotalSalesPerCustomer", SUM('Sales'[Revenue])
    )
RETURN
...

The variable vCustomerSales now holds our first virtual table. It doesn't appear anywhere on the screen, but in memory, Power BI now has a two-column table with 'Customer ID' and 'TotalSalesPerCustomer'.

Step 3: Build Virtual Table to Filter for Top 100 Customers

Now that we have a virtual table of all customers by total revenue for each, it's time to grab only the top 100. To get a list, we use TOPN(). The function TOPN(<recordscount>,<Source Table>,<bywhatcolumn>,<Direction>) lets us specify the number of records, the source table, the field to use for sorting, and the direction of sorting. In our example, we need 100 records. We use the output from our previous table as the source. The third parameter is what field we should use to get the top 100 by, in this case, our newly created virtual column "TotalSalesPerCustomer". Lastly, we choose the sorting order as descending to get the top 100 from high to low sales.

Adding the following line inside the measure now gives us another new virtual in-memory table inside vTop100Customers. Now we can see the power of virtual tables, allowing us to build complex IF and Filter conditions like only giving us the sum or average revenue for these customers instead of applying it to the main table of sales transaction data.

...
VAR vTop100Customers = 
    TOPN( 
        100, 
        vCustomerSales, 
        [TotalSalesPerCustomer], 
        DESC 
    )
RETURN
...

Step 4: Average Revenue of Top Customers

Now, for the final step in our measure. Take 100 records just grabbed from the previous virtual table and then an AVERAGE can be calculated from it. For this, we will use AVERAGEX(). These iterator functions, which end in 'X', allow us to move through a virtual table like a physical column by passing the whole object vTop100Customers.

Using AVERAGEX, we will step through the vTop100Customers table row-by-row and average the values in our virtual 'TotalSalesPerCustomer' column.

Put It All Together

The final measure with its final return:

Average Revenue of Top 100 Customers =
VAR vCustomerSales =
    SUMMARIZE(
        'Sales',
        'Customer'[Customer ID],
        "TotalSales", SUM('Sales'[Revenue])
    )
VAR vTop100Customers = 
    TOPN( 
        100, 
        vCustomerSales, 
        [TotalSales], 
        DESC 
    )
VAR vResult = 
    AVERAGEX(
        vTop100Customers,
        [TotalSales]
)
RETURN
vResult

This is one of the most powerful things in Power BI. You will use it repeatedly as your analysis gets more complex and your skills progress. You can easily modify your request by changing the "SUM" or "TOP", so once created, it becomes invaluable when your boss asks for the same information but with "Top 50" this time! Now, you only need to change one thing, and your reporting goes from one report by hand a week to one in seconds you can re-run on demand anytime!

Tips for Working with Virtual Tables

Variables are Your Friend: Always use VAR variables. They break your DAX into readable steps that are logical in order to process as they compute their logic. It's harder to read and also performs worse the more nested your formulas become.

Use DAX Studio to See Your Virtual Tables: DAX Studio is an incredible free tool for any serious BI Developer. It lets you see and visualize DAX so you can better understand how it is stepping through as you build out complex code. You can make sure that your variable returns all your expected columns and rows, and their values, making the debugging of a complete measure faster and easier.

Iterator Functions like AVERAGEX, SUMX, COUNTX are Essential: Since virtual tables don't have physical relationships to other tables, you must use their iterators to calculate solutions. The iterators are the key as they let you run row by row for any given operation.

Final Thoughts

Virtual tables are a fundamental concept to move from a beginner to an advanced Power BI user. They unlock a new level of analytical depth, allowing you to create sophisticated, multi-step calculations using DAX that directly answer complex business questions without cluttering your data model with physical tables.

While mastering DAX to build these calculations is an invaluable skill, it has a steep learning curve. At Graphed, we’ve created a more direct path to these kinds of insights. Instead of writing complex measures, you can integrate your data sources and simply ask "What’s the average revenue of my top 100 customers?" Our platform translates your plain English questions into the complex analysis needed in the background, generating real-time dashboards and reports instantly. We handle the heavy lifting, so you can focus on what the data means for your business as opposed to wrestling with DAX syntax. You need answers, and we are the easiest way to get answers. Try Graphed now for all your data analytic needs.