Did you know you can divide in Power BI using several different techniques? The fastest is to use the DIVIDE DAX function.
In this Power BI Tutorial, we will learn how to divide two columns using Power BI DAX and Power BI measure divide two columns.
Additionally, we will discuss the topics below:
- DAX divide two columns
- Power BI divide two columns
- Power BI measure divide two columns
- Power BI divide two columns from different tables
- Power query divide two columns
- Power BI divide two columns with filter
- Power BI measure divide one column by another
- Power Query Divide Column by 100
DAX Divide Two Columns
In Power BI, if you want to divide two columns in DAX (Data Analysis Expressions), it means you are creating a new calculation that takes the values in one column and divides them by the values in another column.
Syntax:
DIVIDE(<numerator>, <denominator> [,<alternateresult>])Where:
- DIVIDE = This is a function in DAX (Data Analysis Expressions) used for division operations.
- <numerator> = This is the number you want to divide.
- <denominator> = This is the number you’re dividing by.
- [<alternateresult>] = This is an optional parameter. If the denominator is zero, it returns this alternate result instead of an error message.
Example:
Let’s say you have a table with two columns: “Total Sales” and “Number of Products Sold.” To find the average sales per product, you would divide the total sales by the number of products sold for each row in the table.
To find average sales, we can use the below expression.
Average Sales per Product = DIVIDE('financials'[Total Sales],'financials'[Number of Products Sold])Where:
- Average Sales per Product = This is the result we want, which is the average amount of sales for each product.
- DIVIDE = This is the function we’re using to perform division.
- ‘financials'[Total Sales] = This is the total sales amount from our financial data.
- ‘financials'[Number of Products Sold] = This is the count of products sold from our financial data.
Power BI Divide Two Columns
In this example, we see how we can divide two columns in Power BI.
Imagine you want to calculate the profit margin for each product you sell. To do this, you need to divide the profit made from selling each product by the cost of producing or purchasing that product.
Here, we have an Excel file with Product Name, Cost, Selling Price, and Profit columns.
Now follow the below steps:
1. Open Power BI Desktop and load data using the Get data option. Then, you can see data in the Data pane.
2. Go to the Table view and click the New column under the Table tools.
3. In the formula bar, put the below expression. Then click Commit.
Profit Margin = DIVIDE([Profit ($)], [Cost ($)]) * 100Where:
- Profit Margin = This is the result we’re calculating, which represents the percentage of profit earned from sales.
- DIVIDE = This function divides one value from another.
- [Profit ($)] = This is the total profit earned from sales.
- [Cost ($)] = This is the total cost incurred for those sales.
4. After that, one column is created in the Table view.
This way, you can divide the two columns using the Power BI Divide function.
Power BI Measure Divide Two Columns
Imagine you are managing a small restaurant and want to track the efficiency of your kitchen staff. One way to measure this is by calculating the average time it takes them to prepare each dish compared to the target time for each dish.
Here, we have a SharePoint list (Kitchen Efficiency) that contains below columns with various data types:
| Columns | Data Types |
|---|---|
| Dish Name | Single line of text |
| Actual Time (minutes) | Number |
| Target Time (minutes) | Number |