# Simple Power BI Measures – Beginners’ Tutorial and Best Practices

I’m going to show you some simple Power BI measures you can start with. These can give you a good foundation on what kind of insights Power BI can give you. Understanding these simple Power BI measures is key to easing into more advanced techniques later on.

One thing you have to remember if you’re just shifting to Power BI from Excel is that you won’t be working with individual cells here. Power BI lets you work on columns instead.

In Excel, for example, doing a simple SUM would mean working on individual cells. But in Power BI, you’ll be applying that SUM to entire columns. Once you understand this concept, it changes the way you build your reports from a formula perspective.

To show you how to work on those columns, I’m going to show you a few examples on how these simple measures work. You may watch the full video of this tutorial at the bottom of this blog.

## Average Quantity Example

I’m going to start with an example of how I can get the Average Quantity.

I’ll click on the New Measure icon.

Note that before creating this measure, I made sure that I clicked on my Key Measures first, which is found in the Fields pane. This ensures that once my new measure is created, it lands right into that group.

Average Quantity is just a form of simple averaging.

Using averages is a really simple way to create some of these easy to load formulas. As you can see, a few options automatically pop up when I start typing the word “average”.

The moment I choose AVERAGE, you’ll see that it shows a description of the function. It tells us that this function returns the average of all the numbers in the column.

These descriptions are a great way to familiarize yourself with the different functions you can use in Power BI.

Once I input that function, it automatically gives me more details in terms of what parameters need to go inside it.

I’ll use the Quantity column in this example.

Once I hit enter, that measure will be saved into the Key Measures group.

One important thing to note here is that you’re dependent on the data you have. In this case, I only have my pricing and quantity data. So that makes my options a bit limited.

Just to show you other functions you can use here, you’ll see that you can also type in MIN and you have a number of options showing up.

You can also try MAX.

The other variations showing up for these functions are further covered in the different DAX courses available in Enterprise DNA Online. But for now, I’ll concentrate on the simplest ones.

## Revenue Calculations Using Iterating Functions

Bringing in some revenue information is extremely useful. This will bring in additional data that would give me more insights for further analysis.

To find out whether I can do that, I need to check what kind of information I have inside my table.

Looking at the data, it shows that I can add some revenue information because I have the Quantity and the Price.

Since I want you to truly understand the importance of using measures instead of calculated columns, I’m going to show you a comparison of the two.

I’ll start off by creating a new column.

I’ll call this column Revenues.

To get the revenue, I’m going to multiply Quantity by Price. Note that I’m referencing these specific columns from the Sales table because I’m physically adding data into the table directly.

Once I hit enter, the Revenues column will give me numbers where I could apply the SUM function or maybe the AVERAGE function. This is a calculated column.

But I want to show you that you don’t need to create these columns to get the data you need. This is something that you would normally do in Excel, but the goal here is for you to see the advantage of using Power BI. You can do so much more by using virtual calculations as long as you know which formula to choose.

So I’m going to delete that column I just made.

Then I’ll add a New Measure by clicking this icon.

I’m going to call this Total Sales.

I am also going to add a new line to my formula by doing Shift + Enter. Then, I’ll use the SUM function, then I’ll do a SUMX.

Before continuing, I want to briefly discuss what SUMX does. The basic principle behind SUMX also applies to other functions that have an X at the end of the name.

These functions with an X are called iterating functions. They allow you to run logic at every single row in any table that you specify.

In other words, it’s bringing what you would ordinarily do in a calculated column into a measure.

As you can see, it requires us to reference a table and an expression. The expression can be any kind of logic you have to use to get the results you want.

So I’m going to put Sales since I’m trying to recreate the calculated column I made earlier.

Then, I’ll use the Quantity times the Price.

Once I press enter, that measure is now going to add the logic I just added into every single row.

To show you the results, I’m going to copy and paste the original visualization.

Remember that we have Quantity Sold under Value.

For the duplicate visualization, what I’m going to do is add Total Sales into Value.

Then, I’ll delete Quantity Sold so that I only have Total Sales left.

Now, the results that I’m showing is based on every filter put in place instead of just a one-time general calculation like what happens in a calculated column.

You can see that if you go into the modeling area.

So the relationship flows from the Customers table where each customer is being filtered as it flows into the Sales table as represented by the arrow and the line. This means that I’m only looking at every single sale for that specific customer.

With this measure in place , I can now iterate through every single row. So in the table, the first row is going to compute for the Quantity times the Price. Then, it’s going to do the same thing on the second row, then the third, and so on. It’s going to continue doing that until it has gone through every single sale.

It basically commits each row’s result into memory. And once that’s done, it’s going to sum it all up. Those results are what’s showing up in the visualization below.

I also recommend that you turn your visualizations into tables when you’re starting out with measures instead of the usual charts.

This makes it easier to see the actual numbers instead of having to look deeper into each visualization.

I find it’s just so much easier to try to actually see the number than trying to sort of look deeply into a visualization and understand, okay. What is the formula doing? If you can see each individual result, it makes it a little bit easier to understand maybe what is that actually happening it within a formula.

## Using COUNTROWS To Get Total Transactions

The last formula I’m going to use as an example computes for the Total Transactions. I’m going to add a new measure again for this.

Then, I’m going to use COUNTROWS.

COUNTROWS lets me count the number of rows in a specific table.

Since I need a table for this function, I’m going to reference my Sales table.

Once I push enter, that measure will be added to the Key Measures group. I’ll drag that into my table so that I can start comparing the data.

I’m also going to drag the Quantity Sold for even more insights.

Now, I can see how many purchases each customer has made and how many products have been sold. Because I placed them side by side, it’s also easier to compare the numbers.

## Conclusion

Since this is a beginner’s course, I just gave you a quick overview of some iterating functions you can use in your Power BI measures. These examples already show you how much potential your reports have if you have a basic understanding of these functions.

There’s an entire group of iterating you can use aside from SUMX like AVERAGEX, MAXX and COUNTX, among others. From here, you can jump to more advanced measures and get even more insights.

Again, when working with Power BI, the key is to keep things simple first. Mastering the simple techniques you can apply will make it easier for you to play around with more advanced calculations.

All the best,

Sam

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## Microsoft Flows: Editing And Tracking In Power Automate

Once you understand how a Flow Diagram looks like, it will be easier to make some changes to it. In...

## Creating Power BI Reports Effectively & Avoiding Hidden Pitfalls

In this tutorial, I will discuss four hidden pitfalls in Power BI that can wreck your data model and/or...

## Power BI Page Navigation Buttons

The Power BI page navigation buttons play a critical role when it comes to storytelling. An organized...

## Power BI Report Examples And Best Practices – Part 1

In today's post, I'd like to present some Power BI report examples and best practices. In my own Power...

## Power BI Python Tutorial: How To Translate Texts

This blog will demonstrate how to perform language or text translation using Python and pipe it over...

## Measure In Power BI: Optimization Tips And Techniques

In this tutorial, you’ll learn how to optimize a measure in Power BI. Optimizing measures in your...

## How To Use Power Query Row And Column Selection

This tutorial will discuss how to use selection and projection inside the Power Query Editor. Selection...

## Use Tabular Editor To Create Calculation Groups In Power BI

Today's blog post will give you an introduction to calculation groups. I'll try to answer four basic...

## Effective Data Storytelling: Asking The Right Questions

To ensure that we have a good story to tell, effective data storytelling by asking the right questions...

## The Top 5 Power BI Alternatives in 2023

Power BI has established itself as a powerful business analytics platform, offering a wide range of...

## Turning Calendar Type Layout Into Tabular Format In Power BI Using Query Editor

Analyzing the data that we have can be difficult if it is not correctly arranged. In this tutorial, I...

## Create A Lookup Table From Subtotals: Optimization In Power BI

In this tutorial, we’ll go through a technique that will create a lookup table from subtotals using the...