You are reading the article Power Bi Virtual Table updated in February 2024 on the website Tai-facebook.edu.vn. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 Power Bi Virtual Table
Power BI virtual table is my personal favorite DAX topic. They’re the key to unlocking the full power of DAX. Virtual tables are the only type of tables within Power BI that are fully dynamic, and there are problems that can only be solved by applying virtual table techniques within your measures. You can watch the full video of this tutorial at the bottom of this blog.
In this tutorial, I’m going to share my top 5 tips and tricks that I’ve accumulated over the years that really have helped me understand and debug what’s happening within Power Bi virtual tables.
The example I’m going to walk through today is from the Enterprise DNA forum, and it comes from a member named Dave C, who works in industrial safety. Dave had a series of safety scores and he wanted to normalize those so that the top score was 10, and then dynamically come up with the Nth in that list.
Initially, we thought of doing this through a simple RANKX measure, but we later realized that a lot of his normalized values have ties. For example, if you want the seventh item on the list, there’s not going to be a number seven in a RANKX. There’s no easy way to pull that out of a filter condition. So we decided on a TOPN-based measure so that it would always count down the nth number.
This is akin to when you’re pulling the seventh card from a deck, you count out seven cards, and then you flip over that stack of seven, and the card on the bottom is the one you want. We’re going to do the equivalent of that in a TOPN measure.
You can use DAX Studio or the Tabular Editor. In this example, I’m using the Tabular Editor 3 (TE3). It’s technically possible to do this using the Modeling – New Table tab, but that’s going to create physical tables within your data model. You’re going to have to manually flip between that and the editor and it’s just a slow and difficult way to do it.
When you see the dynamic way in which it can be done through an external tool, you’ll see the benefit.
So within the TE3, we create a new DAX query. We can take our initial measure and copy this over to our DAX query.
If you remember, DAX queries always start with EVALUATE. We’re going to get an error initially because DAX queries return tables. This was a measure with the last two variables that are scalars. What we can do here is change the return value, which is my next tip.
You can debug virtual tables in much the same way as you do with measures – piece by piece, by changing the return value. Let’s start with the first virtual table, the vEvalTable. We simply replace the RETURN value (Final) with our first variable (VAR). And you can see that the error goes away because the DAX query is now getting a table.
In the vEvalTable, we’re taking the original data, which are the safety scores, and we’re normalizing those and adding that Normalized Value column to the virtual table. We’ve got the Index, the Value of the Region, and the Normalized value. We can sort these values up or down and filter the values as well.
This is giving us exactly what we’d expected. It returns 50 rows, which is the full data set. That’s all going well, so let’s go down and explore the next table, which is the vTableTopN. In this table, we’re taking TOPN using the nth item slider value. In this example, we have that seventh value of the virtual table above (vEvalTable), and we’re taking that TOPN based on the normalized value in descending order.
So, when we change our RETURN function into that, it falls off and we don’t get anything. Let’s take a look at why because this is a really important concept for debugging and understanding virtual tables.
If we look at the formula, we have the Nth Item Slider Value as the main suspect here. Back to Power BI, we can see that this is basically just harvesting the number seven. Note that sliders exist within the context of a page.
And so, in this case, when we’re looking at debugging that table out of context, that selected value has no context around it. It doesn’t have anything in terms of being able to pull that number. We were getting a TOPN, but we don’t know what the N is in TOPN because that selected value is returning a blank.
How do we handle that? Let’s look at the selected value measure. Most of the time, we always pay attention to the first parameter in SELECTEDVALUE, but there’s a second parameter, which is an alternate. This brings us to my third tip.
What happened here is that it has been pulling the blank as the alternate. But what we want to do (for debugging purposes) is that we want to put a real value in here. So, we put the number 7 and save that.
Now we have some values. It’s returning seven rows, which is exactly what it should because of that TOPN value of seven.
Let’s continue down the line to the next virtual table, which is the vTableNthItem. We’ve got that stack of seven cards, and this table is basically flipping it over. We were in descending order in the previous table, and now we’re in ascending order.
If we take and copy this down to the RETURN section, we get the results. It’s interesting that it’s not returning one row. It’s returning three rows because these three are tied. That’s exactly the reason why we use TOPN rather than RANKX, in this case.
Now let’s go to Result. If we copy the VAR Result down to the RETURN section, this gets to my fourth tip.
Within the formula, we’re taking the max (MAXX) of that vTableNthItem and we’re returning the normalized value. This could be MAX, it could be MIN, it could be AVERAGE. It’s just some aggregator that’s returning that one value in that table. And so, if we copy this down, it’s going to give us an error because this is now a scalar.
But this is my fourth tip, which is in the context of debugging. What you can do is just add the curly brackets. By doing so, it turns that scalar into a table.
And then, what we’ve got here is just a final error check, which is if it turns out that the evaluation table is smaller than the number of rows, it will return insufficient data. But we know in this case that our data set is big enough. However, we can just test that by typing in Final. Again, because that’s a scaler, we also need the curly brackets, and we get the same value here.
We’ve delved in and debugged this virtual table, and we’ve used the alternate value in the SELECTEDVALUE to keep it from falling over out of context. Now I just want to show you one additional tip that I found really useful.
In the context of doing your debugging, you’re going to want to see in Power BI what that table looks like. The general rule is that a measure can only return a scalar, not a table. But, there’s one cheat that I’m going to show you that allows it to quasi return a table.
Let’s take a look at this measure, which is Visualized Virtual Table, and we’ve got here all the virtual tables that we had initially. For example, we want to display, let’s say on the front report page, the vTableTopN.
You can use this CONCATENATEX function. You can actually take that virtual table name (vTableTopN) and take the values in that table and concatenate them. You can create something that basically looks like a virtual table.
If we take this measure, we need to go back to Power BI and drop this into a card measure. Typically, the table gives an error, but through CONCATENATEX, it turned that table into a scaler. You can see that it’s fairly primitive, but it’s returning exactly what we expect and it’s doing so in a dynamic way.
It is a way to push a table into a measure and show that in your report. It’s a really helpful debugging trick. It will provide a good format in a card value that you can use in a report.
Hopefully, this tutorial gives you some food for thought in terms of working with a Power BI virtual table. These are some additional tips and tricks for understanding what’s going on within your virtual tables. I hope you found that helpful.
Visit our website for more Power BI tutorials and check out the links below for more related content.
All the best!
You're reading Power Bi Virtual Table
I will show you formatting tricks on how to put think borders on matrix visualizations in Power BI. Evidently, this is a little bit different tutorial compared to what I normally post but it can significantly change the visual of your reports. You can watch the full video of this tutorial at the bottom of this blog.
Let’s take a look at the data.
It is a very simple data model consisting of a calendar table, and we got the calendar for this year.
To illustrate, we have the Weekday column that I’ve formatted to return back to the day of the week, we have the Month column that I’ve formatted to return back to the month, and the Month Sort column which is set to be sorted by Month Sort column.
I created three measures, Metric 1, Metric 2, and Metric 3, and then I just formatted this visualization as a matrix. Afterward, I set the row to be the Weekday, the columns to be the Month, and the three measures as values.
With this in mind, it shows a very simple kind of matrix that is fairly common.
Visually speaking, the issue with this matrix is that it’s a little bit difficult to tell which measure belongs to which month. It’s not that obvious.
If we want to make it a little more obvious, Power BI does have the concept of gridlines.
As a matter of fact, I just turned on the Vertical gridlines, and it’s noticeable that they are very faint by default but at least it gives some distinct way to group these together visually.
To make it more visible, go to Visual then select Grid settings. We can change those gridlines to a darker color.
For this example, let’s set the color to black, and now it’s a little more obvious compared to before.
However, these lines have the same sizes as others and it’s still not really what I would want.
If I was in Excel, I would probably grab these cells and then I would put a thick border around them to make it really obvious that these things belong together.
I tried to find something like this but I can’t set this to the border thickness. We can’t do this per individual column or under the Grid settings. I’ve looked around and I haven’t been able to find anything that allows me to really do what I want to do.
But nothing to worry about, there is a formatting trick that I will show you that allows us to achieve our desired result.
To create the thick borders, let’s select this visual and copy it to a new page.
Next is to select the center alignment for the headers.
To achieve this effect, we start by creating a new measure. It does not matter what you call this measure and what character you use. You can use a space, a period, or whichever you prefer.
For this example, let’s name it Separator 1 and I like to use the pipe character for this.
After creating the measure, let’s drag this to the Values section and add it to the bottom.
Now we can see we have the Separator 1 column with these pipe characters in it.
We can also rename this to a pipe character and now we have this extra column.
The next step is to go to Specific column and select the pipe character. Then, go to Values to change the background color to black.
Finally, we now have a much better-looking visual and I can tell in an instant that these things are grouped together.
Putting simple formatting like thick borders can make your report visually appealing. It looks clean and it’s a natural grouping of values because we know exactly which values belong together in an instant. We also have more visualization options in Power BI that you can explore.
This is a pretty good tutorial that’s why I like to share it with everyone. I hope this will be beneficial for you too. Please don’t forget to subscribe to Enterprise DNA TV Channel for more valuable content like this.
I’d like to continue exploring the dynamic uses of the DAX function COLUMNSTATISTICS() – a new, almost entirely undocumented DAX function added to Power BI in August 2023 that has some very unique aspects that I think will be very interesting to get familiar with. You can watch the full video of this tutorial at the bottom of this blog.
I’ve done a lot of experimentation and learned some really interesting new things about how this function works and what you can do with it. In my first blog about this function, I talked about some static uses. Today, I’m going to talk about some really pretty amazing dynamic uses of the function in ways that even IntelliSense is not yet up to date on.
For every table and field in your data model, COLUMNSTATISTICS produces a table of six additional fields that has a table name, column name, min and max values for the column, cardinality, and a max length. That doesn’t seem like a lot of data, but there’s a lot you can do with that.
The data that I’m currently using is just the practice data set for the Enterprise DNA External Tools. It’s a relatively simple data model with five dimension tables, a fact table, and a measure table. A model like this is not that hard to keep track of, but a complex model needs a much more complex dataset monitoring.
The metadata for a complex dataset becomes a lot more important in terms of looking at cardinality, which is the number of unique values within a field. It has a big influence on the size of your model as well as potentially the speed of processing.
In addition, as you’re building a model, it helps to know not just the number of tables, but the date range of those tables. As you build out your Dates table, you’re making sure to cover the full range of data in your fact table.
And so, for this example, let’s try to add a table to this data model that I’m working on. In the Power Query, we’ll go New Source, then Blank Query. And then, we’ll go into the Advanced Editor and I’m just going to paste in a function that is called List.Dates. We’re going to use that to create a table of the cardinality of 10,000, so that’s 10,000 unique dates.
It’s going to return a 10,000-item list and we can just convert that to a table, and then rename that table.
The reason why this is fully dynamic is that we’re doing it all with measures. The interesting thing is that it shows a lot of errors, and the function is so new that IntelliSense is not picking it up properly, but it actually does work.
Let’s go into Tabular Editor 3, which I think is the best way to figure out what the complex DAX measures are actually doing.
Remember that the DAX query returns tables rather than scalers. So, if we take this measure and copy this over into a DAX query, it shows an error because the Result here is still a scaler. We can use the debugging approach of replacing that RETURN Result with individual components of the measure.
In this case, we’ll replace it with ColStats, which what we’ve done at the top is just put that COLUMNSTATISTICS DAX function into a variable. With that, we get exactly what we expect, which is the standard column statistics table.
And now, we want to just look at the Table Name column, and we want to take the distinct values out of that and count those. That’s going to be the number of tables in our data model.
First, we’ll select columns on our ColStats table, and just return that table name field. Typically, in a DAX measure or a DAX query, you want to return a field name with the table name in front of it. But in this case, we don’t know what the appropriate table name references because it only exists virtually. It doesn’t seem to accept the variable name as the table name.
So in this case, we’ve got to stay with what looks like a measure, but it’s actually a column reference without the table reference in front of it. And that still works even though it’s confusing in the nomenclature. Now, if we replaced this RETURN statement with our TabCol, we get exactly what we expect, which is that model tables field.
And then for the Result, we’re just doing a count of the distinct table columns. So, if we replace this with DISTINCT(TabsCol), we get our seven tables.
Now let’s take a look at the highest cardinality in the highest cardinality table and see how we got those. This is actually an important pattern that you can use for a lot of different things where you’re looking for, not just the max number, but the max attribute associated with that number.
So let’s take a look at the MaxCardinality. Even though this is a scaler, we can turn it into a one-cell table by just putting brackets around it. And if we hit five, we’ll see that’s the cardinality value of 10,000.
So now the question is, how do we take this and return the table associated with that cardinality.
To do that, we use this very common pattern using TOPN. We then copy it over to our RETURN statement and it will give us the row we were expecting to get, which is the 10,000 Dates column. Instead of getting one row (because it’s TOPN), we get two because there’s a tie.
What ColStats also does is it creates, for every table, an index in a hidden index row called RowNumber with that being a unique identifier for each row of the table. So that MaxCardinality is always going to be mirrored by the RowNumber if that is a unique identifier.
And so, it doesn’t really matter that we’ve got two rows because what we’re looking at is the Max of Table Name. And that max is there just to return some value that otherwise this would just be a naked column. But we need to put some aggregation around that, and in this case, we use MAX.
Then, if we place MaxCardinality in our RETURN statement, and put it in brackets to return a table rather than a scaler, it returns the value of Test.
This TOPN DAX pattern is a really good pattern to remember when you want to basically figure out a max or a min value, and then return an attribute associated with that min or max.
I’ve given you the general approach and context that we can use in terms of you making the best use of the DAX function COLUMNSTATISTICS in a dynamic way. Being able to track this dynamically in terms of a complex model, I think there’s tremendous value in this function.
All the best!
Microsoft Power BI or Power BI as we know it, is one of the world’s most popular business intelligence tools that allow users to analyze data and share insights.
It is a collection of software services (SaaS), apps, and connectors that work together to turn your data into visually immersive and interactive insights.
Power BI takes business intelligence to a new level and is designed to be used by business professionals with varying levels of data knowledge.
Its dashboard is straightforward, and intuitive and is capable of reporting and visualizing data in a wide range of different styles, including graphs, maps, charts, and more.
Whether you’re a small business owner or a large enterprise, Power BI can help you make data-driven decisions. It provides a comprehensive view of your data, making it easier for you to identify trends, patterns, and insights. With Power BI, you can collaborate with your team, share reports and interactive dashboards, and access your data from anywhere, at any time.
In this article, we will explore the top features and functionality of Power BI, and how it can benefit you and your business.
Let’s get to it.
Power BI Desktop home screen.
Now, let’s check out some of the key features and benefits of Power BI
Power BI sales dashboard example withing Power BI.
Connectivity to a variety of data sources
Data modeling and transformation
Customizable visualizations and dashboards
Collaboration and sharing capabilities
AI-powered insights and recommendations
Power BI offers a number of benefits to businesses that use it.
Key benefits include:
Improved data visibility and insights
Increased efficiency and productivity
Better decision-making capabilities
Improved real-time collaboration and communication
Reduced costs and improved ROI
Overall, Power BI is a powerful tool that can help businesses gain insights into their data and make data-driven decisions.
With the addition of Artificial Intelligence, you will find your data preparation efforts will be supercharged in the BI platform.
Power BI works by connecting to various data sources so you can, well, extract data from them. We’ve broken compatible data sources into 5 main groups, including:
An overview of Power BI
Excel (XLSX, XLSM, XLS, CSV)
Azure SQL Database
Microsoft Azure (Blob Storage, Table Storage, Data Lake, Cosmos DB)
Amazon Web Services (S3, RDS)
Google Cloud Platform (BigQuery)
SharePoint Online (Lists, Excel files)
Web pages (by using the Web connector to scrape data)
R and Python scripts
On-premises data sources (using Power BI Gateway):
SQL Server Analysis Services (SSAS)
SAP Business Warehouse
SharePoint on-premises (Lists, Excel files)
Please note that support is added for new data sources all the time, and you also have the option to use custom or create your own connectors using Power Query M or the Power BI REST API.
Utilizing DAX in Power BI
Column chart (stacked, clustered, 100% stacked)
Bar chart (stacked, clustered, 100% stacked)
Area chart (stacked, line, 100% stacked)
Map (with bubbles, heatmaps, or custom shapes)
Filled map (choropleth)
Tables and matrices
Paginated report table
Cards and gauges
KPI (Key Performance Indicator)
Slicers and filters
Relative date slicer
R script visual
Python script visual
Play axis (for animations)
Numerous custom visuals are available in the Power BI Visuals Marketplace, including Gantt chart, Sankey chart, Word cloud, Histogram, and more.
Now you have created your visuals, it is time to get sharing.
Finally, Power BI allows you to transform your data analytics and share your visualizations with others in a variety of ways, whether on windows or ios.
Firstly, you can publish your reports to the Power BI service, which allows others to view your reports online and is the primary way Power BI users share their dashboards.
Furthermore, you can also export your reports to various formats, including PDF and Excel, or embed them in other applications using Power BI’s APIs.
To get started with Power BI, you need to install the Power BI Desktop application on your computer.
The installation process is straightforward and can be completed in a few steps. First, visit the Microsoft Store page and download the Power BI Desktop application. Follow the prompts to complete the installation process.
Once the installation is complete, open the Power BI Desktop application and sign in using your Microsoft account.
Next steps? Try our free course on Power BI to get a good grasp of the basic concepts, and you will be a Power BI pro in no time!
Creating a date table in Power BI, one of the first things you will learn.
Microsft Power BI has revolutionized business analytics by allowing you to create customized reports and dashboards that can be used to visualize, analyze and share metrics and data.
Once you have imported the data, you can use the drag-and-drop interface to create visualizations such as charts, tables, and maps.
Dashboards are collections of visualizations that can be used to monitor key performance indicators (KPIs) and track progress toward business goals.
To create a dashboard, you need to add visualizations to a canvas and arrange them in a way that makes sense to the user. It really is that simple.
Power BI interface up showcasing data.
Once you have created a report or dashboard, you can publish it to the Power BI service and share it with others.
To publish a report, select the Publish button in the Power BI Desktop application and follow the prompts to upload the report to the Power BI service.
To share a report, you can either share a link to the report or embed it in a website or application.
Data Analysis Expressions (DAX) is a formula language used in Power BI that allows users to create custom calculations and measures.
DAX includes a range of functions and operators that enable users to manipulate data and perform complex calculations.
DAX formulas can be used to analyze data from multiple tables and create dynamic visualizations.
Some of the key features of DAX include:
Aggregation and filtering of data
Calculation of rolling averages, year-to-date totals, and other complex calculations
Integration with Excel formulas and functions
A great example of a Power BI dashboard for a hotel.
Power Query is a data transformation and cleansing tool that allows users to extract data from multiple sources, transform it, and load it into Power BI for analysis.
Power Query includes a range of data transformation functions and connectors that enable users to clean and shape data for analysis.
Some of the key features of Power Query include:
Integration with a wide range of data sources, including SQL Server, Oracle, and Excel
Data cleansing and transformation using a range of functions and operators
Automatic detection and correction of data errors
The Power BI mobile app allows users to access and analyze Power BI reports and big data dashboards on the go.
The app includes a range of features that enable users to view and interact with data on their mobile devices.
Some of the key features of the Power BI mobile app include:
Offline access to reports and dashboards
Integration with mobile device features such as GPS and camera
Ability to share reports and dashboards with others
Power BI Embedded is a cloud-based service that allows users to embed Power BI reports and dashboards into their own applications.
This enables users to integrate data analysis and visualization capabilities directly into their own software products and workspace.
Some of the key features of Power BI Embedded include:
Integration with a wide range of programming languages and frameworks
Customizable visualizations and branding options
Scalable and flexible pricing options
Another great example of using Power BI, this time for sports.
There are 4 versions of Power BI, we will give you an overview below:
Power BI Pro – This is a subscription-based service and offers a comprehensive list of features and functionality. You can access the full power of Power BI with this subscription. Prices ( at the time of writing) are $9.99 per user, per month (billed annually) or $20 per user, per month, billed monthly.
Power BI Premium – Designed for companies with a need for dedicated data needs, dedicated cloud capacity, and high performance. Pricing starts at $20,000usd per month, billed annually.
Simply put, Excel is best suited for small to medium-sized data sets and simpler tasks.
SQL is a language used to manipulate data with a focus on data management, not visualization.
Power pivot is an Excel add-on that enables you to easily analyze and manipulate large volumes of data. It basically increases Excel’s capabilities and functionality and allows you to pull in data from various sources.
Power View is an excel feature that was integrated back in 2013; it is now basically redundant as all of its features were incorporated into Power BI.
Here are the best 10 Power BI certification courses for the year 2023 that you need to be aware of
Do you want to put your Power BI talents to the test? Take the best Power BI certification training available and work towards Microsoft Power BI certification. A Power BI certification course will demonstrate to employers that you know your stuff while also assisting you in becoming a more efficient and productive Power BI user.
Here are the Best 10 Power BI Certification Courses for 2023. These Best 10 Power BI Certification Courses will look great on your CV regardless of whether you’ve only recently started learning Power BI or are a seasoned power user.
Best 10 Power BI Certification Courses for 2023
Which Power BI training certification is the finest one available? There are several options for Microsoft Power BI certification; we’ll just discuss the very best.
edX’s Analyzing and Visualizing Data with Power BI
Best for: Beginners, pupils, researchers, marketing analysts, data analysts, and financial experts.
A four-week, self-paced course on using the Power BI tool for data analysis and visualization. The ability to deal with data, produce visualizations, and develop and distribute dashboards will be taught to the students.
The Professional Certificate in Data Visualization with Power BI from edX
Best for: Intermediate data analysts who want to be certified as Power BI experts
Learn the fundamentals of data literacy as well as how to analyze and visualize data with Power BI. a two-month, self-paced Power BI training including two skill-building classes and professional instruction. Ideal for people who wish to learn more about data analysis as well as data visualization.
The Power BI Training from MindMajix
Best for: Beginners curious to learn more about employment in data analysis and data warehousing
Price: US$250 for live online; US$150 for self-paced.
Work through practical Power BI assignments and projects that include current business use cases. This course has flexible scheduling, 20 hours of sessions, 16 hours of labs, and a free demo available upon request. The curriculum is designed to lead to certificates.
The Power BI Certification Course from Simplilearn
Best for: Business professionals, data analysts, and data professionals
Price: US$799 for self-paced courses and US$999 for online boot camp
With the help of this Power BI Certification Course, you can create Power BI reports, analyze data, and investigate practical use cases. Students will receive a certificate for the course after six hours of enriching learning that is recognized by the industry. The training is offered both independently and through an online boot camp.
The Data Analyst Master’s Programme at Simplilearn
Best for: Professionals who want to master data analysis and learn more about Power BI.
Move past Power BI. Students will learn business analytics, fundamentals of programming, Python, Tableau, Excel, and (of course) Power BI in this all-encompassing course. This training program equips students with the skills they need to succeed in the profession of data analysis and includes unique diplomas for each technology.
Introduction to Power BI via Datacamp
Best for: New users of Power BI who want to get started
Six hours of courses, 56 tasks, and 19 videos will help you understand Power BI. With practical exercises and real-world scenarios, students will learn Power BI and leave with an understanding of everything from Power BI reports to DAX queries.
Microsoft Power BI: A Complete Introduction on Udemy
Best for Analysts who want to understand Power BI completely
Experience level: Beginner
This course offers tips on how to conduct business analysis with Microsoft Power BI. Along with learning how to use the many tools of the Power BI platform, students will be guided through the process of aggregating, gathering, and reporting data.
Business Intelligence with Microsoft Power BI Desktop on Udemy
Experience level: Beginner
In this course, business analysts will learn how to utilize Microsoft Power BI to produce reports of a professional caliber and how to design interactive, stunning dashboards. They will learn more about the data visualization pipeline firsthand from a seasoned Power BI developer.
Effective Reporting with Power BI from PluralSight
Best for: Intermediate users who want to utilize Power BI to create reports and have some knowledge of data analysis.
Price: US$29 per month
Learn how to use Power BI for successful reporting. The principles of Power BI, the development of simple and complex reports, the management of atypical data, and the use of key performance indicators (KPIs) are all covered in this course. This course will teach individuals everything, from simple reports to dynamic dashboards.
Getting Started with Power BI Desktop on Coursera
Best for: New users who wish to familiarise themselves with the Power BI Desktop platform
Price: US$39 per month
This tutorial will review how to use TOPN as a virtual ranking function to generate interesting insights based on ranking logic in Power BI. You may watch the full video of this tutorial at the bottom of this blog.
Using TOPN as a virtual ranking function allows you to dynamically produce the top and bottom results for any measure.
This example will show how to work out the locations that generate the highest and lowest revenue per customer.
This table contains the Revenue for each customer.
You want to work out which locations have the highest and lowest Revenue.
Let’s say that a customer bought your products from a range of different locations.
Using Stephen Howard as an example, you can see that he bought products from six different cities:
You now want to know how much revenue was generated for each city. Then, you want to virtually rank the cities and calculate which of them belong to the top and bottom two.
This formula counts the number of locations a customer has purchased from:
The COUNTROWS function works out each unique place where a product has been bought.
However, this formula can’t give you with the ranking results you need.
This is where the TOPN function comes in. It allows you to have a virtual ranking inside a formula.
To calculate the top two cities with the highest revenue for each customer, you need to use this formula:
The CALCULATE function computes the Total Revenue using a different context for the top two cities.
Let’s now focus on the TOPN statement in the formula:
The first parameter for this TOPN statement is the total ranking that needs a virtual calculation. Thus, 2 is used to get the top two cities.
If it’s 4, it will return the top four cities.
You need to make sure that you’re only iterating through the places that a customer has purchased from. This is why Index is used rather than an element in the model.
Using Index ensures that you’re only counting the regions your customers have purchased from, and not all the regions in your model.
If you were to put the VALUES function along with the actual Name of the City, you’d get the overall Total Revenue from the top two cities — not from each individual customer.
The TOPN function creates a brand new context for each result in the table.
It’s creating a virtual table containing only the top two locations a customer has purchased from.
This is the formula used to calculate the bottom two cities:
It’s exactly the same formula as the first, but you need to change DESC to ASC.
Here’s how to check if this formula is correct:
If you bring the cities with purchases to the table, you can see that the number of iterations matches the result of the Total Cities.
For example, Aaron Bradley bought from four different locations. So, there are four iterations showing in the second table.
You can see the four different purchase amounts the customer has for each location.
If you calculate and compare the figures of the two tables, you will see that they both match. All of Aaron Bradley’s amounts equal to 173,128.00, which is the Total Revenue. The top two cities have a Revenue of 124, 191.20, and the bottom two 48,936.80.
This tutorial discussed how to use TOPN as a virtual ranking function to create effective ranking visuals in Power BI.
You can wrap the TOPN function in COUNTROWS, SUMX, or AVERAGE to create more valuable insights in your reports. It’s a very flexible and reusable tool to use.
All the best,
Update the detailed information about Power Bi Virtual Table on the Tai-facebook.edu.vn website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!