Master Virtual Tables in Power BI Using DAX

In this blog post, I’ll run through a truly powerful analytical technique which I’m confident will WOW anyone. Virtual tables are a unique analytical technique that you can use to visualize interesting insights inside Power BI. You may watch the full video of this tutorial at the bottom of this blog.

The example I’ll show is just one of the many techniques you can apply. I use the term “algorithms” because you can expand on this and make it even more advanced.

The virtual table algorithms will show how powerful DAX is and how advanced you can get inside of DAX formula.

Table of Contents

Where To Use Virtual Table Algorithms

There are instances where you will want to rank your customers across a number of different variables. But ultimately, you want to bring them back using just one variable.

For this to happen, you need to create an algorithm that enables you to analyze all these different variables and factors according to a dimension (which in this case are my customers).

But then you also want to bring it back to one number. This way, you can gauge if a customer has been good or bad based on this one factor, instead of factoring in three to ten variables.

Sales, Profits & Margins: Looking At The Bigger Picture

For this tutorial, I’ve already covered the sales, profits, and margins. The next thing to do is to create an algorithm within a virtual table that will give us that one number. This number will tell us if a customer has been good or bad.

Sometimes, when we’re looking at one thing in isolation (like sales for example), it does not give us the complete picture. This is because you need to evaluate the profits, where a customer who has produced smaller profits is probably better than someone who has produced a lot of sales.

Margins are also very important. If you’re extracting a very high margin from a customer of lower volume, that customer can be classified as a good customer.

So overall, our goal is to create an algorithm that will look across all these three variables (Total Sales, Total Profits, and Profit Margins) to know who our top customers and bottom customers are.

Determining the Customer Ranking

I’ve already broken down these calculations one by one in the table. We have our Customer Sales Rank, Customer Profits Rank, and Customer Margins Rank.

We’re going to count up these three ranks, and then it’s going to give us the best versus the worst customers.

Customer Sales Rank

Let’s have a look at the formulas I’ve used for each individual measure. I’ve used RANKX, which is perfect for ranking all of our customers versus a particular expression or measure.

For the Customer Sales Rank, we ranked our customers based on their Total Sales from 1 to whatever.

You can see that at the top of the table is William Andrews. He is our top customer so he is ranked 1.

Customer Profits Rank

We applied the same technique from the previous measure to come up with our Customer Profits Rank.

We can see here that our top customers are not really our top customers by margin. Their margins are actually a lot lower.

So if we look at our top customers by margin, they’re actually much lower in terms of sales.

Using Virtual Tables

As I have mentioned earlier, we want to create this one number and I will show you how to do it using a virtual table. There are a couple of ways to do it, but using virtual tables can simplify your formula.

In reality, you won’t even need to create or break out each of these individual formulas. You can put them inside a virtual table, and then utilize the columns that you put inside your virtual tables.

Inside this one formula (which I’ve called Overall Ranking Factor), I have used VARIABLES to create individual formulas such as the Customer Sales Rank, Customer Profits Rank, and Customer Margins Rank measures.

Using SUMX As An Iterating Function

This is the part where I used a virtual table to do a sum of all these different customer ranks. I also needed to create an iterator so this is where the SUMX function comes in.

With SUMX, we need to iterate through a table, right? We can do this with a virtual table. What’s amazing about virtual tables is that we can put in any table of our making.

What I’ve done is to create a virtual table where SUMMARIZE allows me to create this table of all the rankings. I can create it virtually without having to reference a calculation or measure individually. It’s all within this one measure.

I’ve managed to create a virtual table which lists out the Customer Name, Sales Rank, Profit Rank, and Margin Rank one by one, and next to each other.

What’s also amazing is that within this iterating function, we can iterate through all of our customers, and then reference the columns that we have placed within the virtual table.

This will sum up all the different ranks and internal calculations within a single measure. By utilizing this technique, you won’t need to break it down into multiple measures.

You can just create this one measure which encompasses all the different calculations that you want to add to your algorithm. You can create very advanced and complex algorithms, and then put them all into one neat measure.

Finally, we can bring the Overall Ranking Factor measure into our table. You can now see the output of the algorithm we have just created and utilize it in our analysis.

It’s just one number versus all the numbers that came from our sales, profits, and margins. For example, our customer Peter Boyd is ranked 36th in sales, 8^th in profitability, 29^th in margin ranking, with an overall rankling of 73^rd. This is how we classify our top customers using all these factors.

Benefits of Virtual Tables

Many Power BI users will not even realize that you don’t have to always only run calculations and advanced logic through columns or tables that are physically in your data model.

You can create virtual tables and then run logic through these tables even though they do not exist physically anywhere inside your model.

These tables are a perfect and fast way to run advanced logic that may produce insights that can be utilized and acted upon in a variety of different scenarios.

Conclusion

With Power BI, you get to create more advanced algorithms within measures. Banks or insurance companies can greatly benefit from this technique because they’re always trying to rank things and run algorithms based on a number of different factors.

They can find out how likely someone is going to default, or how likely they are going to have to pay out an insurance claim. Being able to implement these types of calculations within measures is really powerful.

If you can understand how this works inside Power BI, specifically with measures, you are on your way to developing some incredible analytical work inside Power BI.

For many more advanced analytical techniques for Power BI, check out the below course module located at Enterprise DNA Online.

Advanced Analytics in Power BI

Sam