CONTENT
How to use Power BI DAX
This blog guides you through solving common Power BI questions with DAX, covering calculations, totals, and logical functions.
Saartje Ly
Data Engineering Intern
July 23, 2024
Download Data Files
First download the files from here.
1. Open Power BI, and click Import Data from Excel.
2. Click on the Cookie Types file, choosing the sheet called Cookie Types, then click Load.
3. Bring in the other two sheets using the Excel Workbook button under the Home tab.
4. Head to the model view on the left pane. You'll notice there isn't yet a relationship between Cookie Types and Orders, however, Cookie Type in Cookie Types and Product in Orders are the same thing. Click and drag Cookie Type over to Product to form a relationship.
This created a one to many relationship. So for each cookie type, that cookie can appear on many different orders.
5. Head back to the table view from the left pane.
If you click on the Orders table in the right pane, you'll see the Units Sold column, telling us how many cookies of what type got sold. So we want to know how many total cookies got sold across all of these different orders i.e. we want to sum up the Units Sold column. We are going to use a measure to do that.
Total units sold
1. Click back onto the Report view from the left pane, right click on the Orders field in the right pane, then choose New measure.
2. Name this measure Total Number of Units Sold, and equal it to a SUM function. In Power BI when you use a SUM function, it's summing up the entire column or all the values that are a part of that field. Put your Units Sold column inside this SUM function and hit enter.
3. Tick the box next to your new Measure, then change it to a matrix by clicking on the graph created and choosing matrix in the visualizations.
You can also pull in more fields to visualize the data differently.
4. Open the customer view to the right and tick the box for Name. Here we'll be able to see the Name next to the total number of cookies ordered.
Looking at Total Number of Units Sold, you'll see that we have decimal places - however we can't sell a fraction of a cookie. We want to hide these decimal places.
5. To change the formatting of a measure, go to the right hand pane, clicking on the new measure that we just created. This opens a tab called measure tools up the top. Click on the up arrow where the decimal places is set to Auto. This sets the decimal places to 0.
This formatting holds for whenever you use the same measure in other graphs.
6. Let's also click on the comma button to add commas to the numbers.
7. Let's say you wanted to change the title of your measure. Simply click on your measure in the right hand pane to edit up the top.
We've created our first measure but let's create some more!
We are now curious as to how many orders we have.
Number of orders
1. Right click on the Orders table, and choose New measure
2. Name this measure Count of orders, then equal it to the COUNTROWS function, specifying the Orders table.
3. Once again, insert a matrix, and select the new measure - Count of orders.
Feel free to pull in other information such as Customer Name.
Distinct customers
1. Head back to the Order table in Table view. As you can see for the Customer ID column, we have a unique ID which distinguishes between customers. Let's say we want to know how many distinct customers we have. Go back to the Report view.
2. Right click on the Orders table and click New measure.
3. Name this measure Distinct Customers, equaling it to DISTINCTCOUNT. This function counts the number of distinct values in a column. Specify the column name Customer ID.
4. Once again insert a matrix and tick Distinct Customers.
To add a comment, in your expression hit shift + enter, then put two forward slashes.
Profit
Click into the Table view. If you're in the Orders table you'll see that there is a column for Revenue and a column for Cost. We want to use measures to calculate what the profit is. We are going to sum up all the revenue from the column, and then minus the sum of all the cost. Head back to the report view.
1. Right click on the Orders table and choose New measure.
2. Enter this formula below. We are subtracting the sum of the cost from the sum of the revenue.
3. Add Name and Total Profit to the matrix with Distinct Customers. Here we can see who is our most profitable customer.
To calculate the profit, there are two other ways that you can do this as well.
4. In the Home tab, click on Quick measure.
Quick measures are a very fast way to build different DAX formulas.
5. Let's say we want to calculate the total profit. Open the Calculation dropdown and choose Subtraction.
6. Expand the Orders table and drag and drop revenue to the Base value.
7. Drag and drop the cost to the Value to subtract box. Click on Add.
This has now created a new measure called Revenue minus Cost. If you select your matrix and tick the box, you'll notice it has the same values as Total Profit. Additionally, if you click on the Revenue minus Cost field you'll see that we are given an expression identical to the one we gave earlier for Total Profit.
Click back into the Data view, looking back at the Revenue and Cost columns. Instead of adding a new measure, we can just add a new column.
8. To add a new column, click on the Table tools tab, then click on New column.
9. Add the expression below. We don't have to SUM it because it'll just do it by default.
Jump back to the Report view.
Profit Margin %
1. Right click on the Orders table and choose New measure, naming it Profit margin %.
2. We can simply refer to our measure we made, Total Profit.
3. Create a new matrix using Total Profit and Cookie Type.
4. Select Profit Margin % on the right hand side
5. Go to Measure tools up the top, then click the % button inside Formatting.
Iterator functions - Total Profit
Click over to the Data view.
With all the functions we've been running, it's been looking at the entire column. But what if we want to do some calculations on a row by row basis? This is where we can use something called an iterator function. Click on Cookie Types over on the right hand side to see why we'd use iterator functions. Here in this view let's say we want to calculate the total profit.
We want to take the revenue per cookie minus the cost per cookie, multiplied by the units sold per row. Then whatever the profit is, you want to add it to the next row.
Jump back into the report view.
1. Right click on Cookie Types and select New measure.
2. This time instead of just putting in sum, put in sumx. This makes it an iterator function (i.e., row by row). The first argument is the table, second argument is the expression. i.e., our units multiplied by (revenue - cost).
3. Create a matrix with this Total Profit 2.
Time & date functions
Back in the Table view, click on the Orders table.
We want to know - how many cookies do we sell on each day of the week? Within the Order table we want to add a new column.
1. Navigate to the Table tools tab, and choose New column.
2. Name the column Day of week then use the WEEKDAY function. The first argument is the Date, and the second argument is what you want the beginning of the week to be. Select 1 for Sunday, and 7 for Saturday.
We will receive a column with the number representing the day of the week. Now we want to know what day of the week do the most orders occur on?
Click back into the Report view. You'll notice your new Day of week column under Orders to the right.
3. Tick your new column, and it will give you a bar chart.
4. For us to see the day of week down on the axis, click and drag Day of week into the X-axis box under Visualizations.
So now we can visualize when all these different orders occur.
Logical functions
Click back into the Data view to the left, making sure you're in the Orders table.
Let's say we want to find all the different products that contain 'Chocolate'.
1. Once again go to Table tools up the top, and click New column
2. Name this column Has Chocolate and use the FIND function. The first argument is the string to find, the second argument is in what column, the third argument is the start position, and the fourth argument is the value if the string is not found. FIND returns the starting position of this text string in another text string.
If you scroll to the end, you'll notice White Chocolate Macadamia Nut has a value of 7, as "Chocolate" is in position 7.
We now want to use an IF statement to return whether it has "Chocolate" in it or not.
3. Click on your Has Chocolate field. Update it to this expression below. Here we are saying if the Has Chocolate value is above 0, it has Chocolate, else it doesn't have Chocolate.
CALCULATE function
Let's say we wanted to know: How many orders did we have for Chocolate Chip cookies that were also over 500 orders?
1. In Report view, right click on your Orders table and add a New measure.
2. With a descriptive name, equal your measure to the CALCULATE function. The first argument is the expression. We want to know how many orders so, we want to count the number of rows of our Orders table. Our next arguments are filters. We specify if the Units Sold are greater than 500, and if the product column contains "Chocolate Chip".
3. Insert a matrix and tick our newly created measure.
Download Data Files
First download the files from here.
1. Open Power BI, and click Import Data from Excel.
2. Click on the Cookie Types file, choosing the sheet called Cookie Types, then click Load.
3. Bring in the other two sheets using the Excel Workbook button under the Home tab.
4. Head to the model view on the left pane. You'll notice there isn't yet a relationship between Cookie Types and Orders, however, Cookie Type in Cookie Types and Product in Orders are the same thing. Click and drag Cookie Type over to Product to form a relationship.
This created a one to many relationship. So for each cookie type, that cookie can appear on many different orders.
5. Head back to the table view from the left pane.
If you click on the Orders table in the right pane, you'll see the Units Sold column, telling us how many cookies of what type got sold. So we want to know how many total cookies got sold across all of these different orders i.e. we want to sum up the Units Sold column. We are going to use a measure to do that.
Total units sold
1. Click back onto the Report view from the left pane, right click on the Orders field in the right pane, then choose New measure.
2. Name this measure Total Number of Units Sold, and equal it to a SUM function. In Power BI when you use a SUM function, it's summing up the entire column or all the values that are a part of that field. Put your Units Sold column inside this SUM function and hit enter.
3. Tick the box next to your new Measure, then change it to a matrix by clicking on the graph created and choosing matrix in the visualizations.
You can also pull in more fields to visualize the data differently.
4. Open the customer view to the right and tick the box for Name. Here we'll be able to see the Name next to the total number of cookies ordered.
Looking at Total Number of Units Sold, you'll see that we have decimal places - however we can't sell a fraction of a cookie. We want to hide these decimal places.
5. To change the formatting of a measure, go to the right hand pane, clicking on the new measure that we just created. This opens a tab called measure tools up the top. Click on the up arrow where the decimal places is set to Auto. This sets the decimal places to 0.
This formatting holds for whenever you use the same measure in other graphs.
6. Let's also click on the comma button to add commas to the numbers.
7. Let's say you wanted to change the title of your measure. Simply click on your measure in the right hand pane to edit up the top.
We've created our first measure but let's create some more!
We are now curious as to how many orders we have.
Number of orders
1. Right click on the Orders table, and choose New measure
2. Name this measure Count of orders, then equal it to the COUNTROWS function, specifying the Orders table.
3. Once again, insert a matrix, and select the new measure - Count of orders.
Feel free to pull in other information such as Customer Name.
Distinct customers
1. Head back to the Order table in Table view. As you can see for the Customer ID column, we have a unique ID which distinguishes between customers. Let's say we want to know how many distinct customers we have. Go back to the Report view.
2. Right click on the Orders table and click New measure.
3. Name this measure Distinct Customers, equaling it to DISTINCTCOUNT. This function counts the number of distinct values in a column. Specify the column name Customer ID.
4. Once again insert a matrix and tick Distinct Customers.
To add a comment, in your expression hit shift + enter, then put two forward slashes.
Profit
Click into the Table view. If you're in the Orders table you'll see that there is a column for Revenue and a column for Cost. We want to use measures to calculate what the profit is. We are going to sum up all the revenue from the column, and then minus the sum of all the cost. Head back to the report view.
1. Right click on the Orders table and choose New measure.
2. Enter this formula below. We are subtracting the sum of the cost from the sum of the revenue.
3. Add Name and Total Profit to the matrix with Distinct Customers. Here we can see who is our most profitable customer.
To calculate the profit, there are two other ways that you can do this as well.
4. In the Home tab, click on Quick measure.
Quick measures are a very fast way to build different DAX formulas.
5. Let's say we want to calculate the total profit. Open the Calculation dropdown and choose Subtraction.
6. Expand the Orders table and drag and drop revenue to the Base value.
7. Drag and drop the cost to the Value to subtract box. Click on Add.
This has now created a new measure called Revenue minus Cost. If you select your matrix and tick the box, you'll notice it has the same values as Total Profit. Additionally, if you click on the Revenue minus Cost field you'll see that we are given an expression identical to the one we gave earlier for Total Profit.
Click back into the Data view, looking back at the Revenue and Cost columns. Instead of adding a new measure, we can just add a new column.
8. To add a new column, click on the Table tools tab, then click on New column.
9. Add the expression below. We don't have to SUM it because it'll just do it by default.
Jump back to the Report view.
Profit Margin %
1. Right click on the Orders table and choose New measure, naming it Profit margin %.
2. We can simply refer to our measure we made, Total Profit.
3. Create a new matrix using Total Profit and Cookie Type.
4. Select Profit Margin % on the right hand side
5. Go to Measure tools up the top, then click the % button inside Formatting.
Iterator functions - Total Profit
Click over to the Data view.
With all the functions we've been running, it's been looking at the entire column. But what if we want to do some calculations on a row by row basis? This is where we can use something called an iterator function. Click on Cookie Types over on the right hand side to see why we'd use iterator functions. Here in this view let's say we want to calculate the total profit.
We want to take the revenue per cookie minus the cost per cookie, multiplied by the units sold per row. Then whatever the profit is, you want to add it to the next row.
Jump back into the report view.
1. Right click on Cookie Types and select New measure.
2. This time instead of just putting in sum, put in sumx. This makes it an iterator function (i.e., row by row). The first argument is the table, second argument is the expression. i.e., our units multiplied by (revenue - cost).
3. Create a matrix with this Total Profit 2.
Time & date functions
Back in the Table view, click on the Orders table.
We want to know - how many cookies do we sell on each day of the week? Within the Order table we want to add a new column.
1. Navigate to the Table tools tab, and choose New column.
2. Name the column Day of week then use the WEEKDAY function. The first argument is the Date, and the second argument is what you want the beginning of the week to be. Select 1 for Sunday, and 7 for Saturday.
We will receive a column with the number representing the day of the week. Now we want to know what day of the week do the most orders occur on?
Click back into the Report view. You'll notice your new Day of week column under Orders to the right.
3. Tick your new column, and it will give you a bar chart.
4. For us to see the day of week down on the axis, click and drag Day of week into the X-axis box under Visualizations.
So now we can visualize when all these different orders occur.
Logical functions
Click back into the Data view to the left, making sure you're in the Orders table.
Let's say we want to find all the different products that contain 'Chocolate'.
1. Once again go to Table tools up the top, and click New column
2. Name this column Has Chocolate and use the FIND function. The first argument is the string to find, the second argument is in what column, the third argument is the start position, and the fourth argument is the value if the string is not found. FIND returns the starting position of this text string in another text string.
If you scroll to the end, you'll notice White Chocolate Macadamia Nut has a value of 7, as "Chocolate" is in position 7.
We now want to use an IF statement to return whether it has "Chocolate" in it or not.
3. Click on your Has Chocolate field. Update it to this expression below. Here we are saying if the Has Chocolate value is above 0, it has Chocolate, else it doesn't have Chocolate.
CALCULATE function
Let's say we wanted to know: How many orders did we have for Chocolate Chip cookies that were also over 500 orders?
1. In Report view, right click on your Orders table and add a New measure.
2. With a descriptive name, equal your measure to the CALCULATE function. The first argument is the expression. We want to know how many orders so, we want to count the number of rows of our Orders table. Our next arguments are filters. We specify if the Units Sold are greater than 500, and if the product column contains "Chocolate Chip".
3. Insert a matrix and tick our newly created measure.
Download Data Files
First download the files from here.
1. Open Power BI, and click Import Data from Excel.
2. Click on the Cookie Types file, choosing the sheet called Cookie Types, then click Load.
3. Bring in the other two sheets using the Excel Workbook button under the Home tab.
4. Head to the model view on the left pane. You'll notice there isn't yet a relationship between Cookie Types and Orders, however, Cookie Type in Cookie Types and Product in Orders are the same thing. Click and drag Cookie Type over to Product to form a relationship.
This created a one to many relationship. So for each cookie type, that cookie can appear on many different orders.
5. Head back to the table view from the left pane.
If you click on the Orders table in the right pane, you'll see the Units Sold column, telling us how many cookies of what type got sold. So we want to know how many total cookies got sold across all of these different orders i.e. we want to sum up the Units Sold column. We are going to use a measure to do that.
Total units sold
1. Click back onto the Report view from the left pane, right click on the Orders field in the right pane, then choose New measure.
2. Name this measure Total Number of Units Sold, and equal it to a SUM function. In Power BI when you use a SUM function, it's summing up the entire column or all the values that are a part of that field. Put your Units Sold column inside this SUM function and hit enter.
3. Tick the box next to your new Measure, then change it to a matrix by clicking on the graph created and choosing matrix in the visualizations.
You can also pull in more fields to visualize the data differently.
4. Open the customer view to the right and tick the box for Name. Here we'll be able to see the Name next to the total number of cookies ordered.
Looking at Total Number of Units Sold, you'll see that we have decimal places - however we can't sell a fraction of a cookie. We want to hide these decimal places.
5. To change the formatting of a measure, go to the right hand pane, clicking on the new measure that we just created. This opens a tab called measure tools up the top. Click on the up arrow where the decimal places is set to Auto. This sets the decimal places to 0.
This formatting holds for whenever you use the same measure in other graphs.
6. Let's also click on the comma button to add commas to the numbers.
7. Let's say you wanted to change the title of your measure. Simply click on your measure in the right hand pane to edit up the top.
We've created our first measure but let's create some more!
We are now curious as to how many orders we have.
Number of orders
1. Right click on the Orders table, and choose New measure
2. Name this measure Count of orders, then equal it to the COUNTROWS function, specifying the Orders table.
3. Once again, insert a matrix, and select the new measure - Count of orders.
Feel free to pull in other information such as Customer Name.
Distinct customers
1. Head back to the Order table in Table view. As you can see for the Customer ID column, we have a unique ID which distinguishes between customers. Let's say we want to know how many distinct customers we have. Go back to the Report view.
2. Right click on the Orders table and click New measure.
3. Name this measure Distinct Customers, equaling it to DISTINCTCOUNT. This function counts the number of distinct values in a column. Specify the column name Customer ID.
4. Once again insert a matrix and tick Distinct Customers.
To add a comment, in your expression hit shift + enter, then put two forward slashes.
Profit
Click into the Table view. If you're in the Orders table you'll see that there is a column for Revenue and a column for Cost. We want to use measures to calculate what the profit is. We are going to sum up all the revenue from the column, and then minus the sum of all the cost. Head back to the report view.
1. Right click on the Orders table and choose New measure.
2. Enter this formula below. We are subtracting the sum of the cost from the sum of the revenue.
3. Add Name and Total Profit to the matrix with Distinct Customers. Here we can see who is our most profitable customer.
To calculate the profit, there are two other ways that you can do this as well.
4. In the Home tab, click on Quick measure.
Quick measures are a very fast way to build different DAX formulas.
5. Let's say we want to calculate the total profit. Open the Calculation dropdown and choose Subtraction.
6. Expand the Orders table and drag and drop revenue to the Base value.
7. Drag and drop the cost to the Value to subtract box. Click on Add.
This has now created a new measure called Revenue minus Cost. If you select your matrix and tick the box, you'll notice it has the same values as Total Profit. Additionally, if you click on the Revenue minus Cost field you'll see that we are given an expression identical to the one we gave earlier for Total Profit.
Click back into the Data view, looking back at the Revenue and Cost columns. Instead of adding a new measure, we can just add a new column.
8. To add a new column, click on the Table tools tab, then click on New column.
9. Add the expression below. We don't have to SUM it because it'll just do it by default.
Jump back to the Report view.
Profit Margin %
1. Right click on the Orders table and choose New measure, naming it Profit margin %.
2. We can simply refer to our measure we made, Total Profit.
3. Create a new matrix using Total Profit and Cookie Type.
4. Select Profit Margin % on the right hand side
5. Go to Measure tools up the top, then click the % button inside Formatting.
Iterator functions - Total Profit
Click over to the Data view.
With all the functions we've been running, it's been looking at the entire column. But what if we want to do some calculations on a row by row basis? This is where we can use something called an iterator function. Click on Cookie Types over on the right hand side to see why we'd use iterator functions. Here in this view let's say we want to calculate the total profit.
We want to take the revenue per cookie minus the cost per cookie, multiplied by the units sold per row. Then whatever the profit is, you want to add it to the next row.
Jump back into the report view.
1. Right click on Cookie Types and select New measure.
2. This time instead of just putting in sum, put in sumx. This makes it an iterator function (i.e., row by row). The first argument is the table, second argument is the expression. i.e., our units multiplied by (revenue - cost).
3. Create a matrix with this Total Profit 2.
Time & date functions
Back in the Table view, click on the Orders table.
We want to know - how many cookies do we sell on each day of the week? Within the Order table we want to add a new column.
1. Navigate to the Table tools tab, and choose New column.
2. Name the column Day of week then use the WEEKDAY function. The first argument is the Date, and the second argument is what you want the beginning of the week to be. Select 1 for Sunday, and 7 for Saturday.
We will receive a column with the number representing the day of the week. Now we want to know what day of the week do the most orders occur on?
Click back into the Report view. You'll notice your new Day of week column under Orders to the right.
3. Tick your new column, and it will give you a bar chart.
4. For us to see the day of week down on the axis, click and drag Day of week into the X-axis box under Visualizations.
So now we can visualize when all these different orders occur.
Logical functions
Click back into the Data view to the left, making sure you're in the Orders table.
Let's say we want to find all the different products that contain 'Chocolate'.
1. Once again go to Table tools up the top, and click New column
2. Name this column Has Chocolate and use the FIND function. The first argument is the string to find, the second argument is in what column, the third argument is the start position, and the fourth argument is the value if the string is not found. FIND returns the starting position of this text string in another text string.
If you scroll to the end, you'll notice White Chocolate Macadamia Nut has a value of 7, as "Chocolate" is in position 7.
We now want to use an IF statement to return whether it has "Chocolate" in it or not.
3. Click on your Has Chocolate field. Update it to this expression below. Here we are saying if the Has Chocolate value is above 0, it has Chocolate, else it doesn't have Chocolate.
CALCULATE function
Let's say we wanted to know: How many orders did we have for Chocolate Chip cookies that were also over 500 orders?
1. In Report view, right click on your Orders table and add a New measure.
2. With a descriptive name, equal your measure to the CALCULATE function. The first argument is the expression. We want to know how many orders so, we want to count the number of rows of our Orders table. Our next arguments are filters. We specify if the Units Sold are greater than 500, and if the product column contains "Chocolate Chip".
3. Insert a matrix and tick our newly created measure.
CONTENT
SHARE