# 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

## Power BI Trend Analysis: Are Margins Expanding Or Contracting?

In this blog post, I will be diving into a relatively specific insight by conducting a Power BI trend...

## Data Visualization Report Frameworks | Part 2

For today, we'll be continuing the discussion on our enhanced visualization frameworks for Power BI....

## Create A Power BI Sparkline Chart In Report Builder

In this Power BI Report Builder tutorial, youâ€™ll learn how to add a sparkline chart in your paginated...

## Using Iterating Functions SUMX And AVERAGEX In Power BI

One of the most crucial topics for any Power BI beginner to know about is iterating functions. You may...

## PowerApps Documentation: Using MS Docs For Expert Functionalities

Let's talk about Microsoft's PowerApps documentation and what an important resource it can be for users...

## Data Modeling In Power BI: Tips & Best Practices

In this tutorial, you'll learn valuable tips and best practices for data modeling in Power BI. You can...

## Using The Query Builder Feature In DAX Studio

In this tutorial, youâ€™ll learn how to use the Query Builder feature to easily create queries and...

## Time-Related Insights From Your Supply Chain Metric

For this tutorial, I'm going to cover some high-quality time-related insights directly from your supply...

## Create A Multilingual Power BI Report

For today's blog, I want to discuss a not-unusual situation with many of my consulting assignments for...

## CROSSJOIN DAX Function: Server Timings & Query Plan

In this tutorial, youâ€™ll learn how the CROSSJOIN function works in DAX using the Server Timings pane...

## Overview Of The DAX Studio Keyword COLUMN

Another important keyword to learn when using DAX Studio is the COLUMN keyword. In simplest terms, the...

## Python Cheat Sheet: Essential Quick and Easy Guide

When programming in Python, having a reliable cheat sheet by your side can be a game-changer. Python...