Power BI divides two columns [With 14 real examples]

In this Power BI Tutorial, we will learn how to divide two columns using Power BI DAX, and how to divide two columns with the filter in Power BI with various examples.

And also will cover the below-mentioned topics as well:

1. Power BI DAX divides two columns
2. How to divide two columns in Power BI
3. Power BI divides two columns to measure
4. Power BI divides two columns of percentage
5. Power BI divides the sum of two columns
6. How to work with Power BI divides two calculated columns
7. Power BI divides two columns with a filter
8. How to work with Power BI divides two columns by each row
9. Power BI divides two columns from different tables
10. Power Bi divides two columns in a table
11. Power Bi divides two columns in a matrix
12. Power Bi divides two columns with a filter
13. Divides two counts in Power BI
14. Power Bi divides one measure by another

Power BI DAX divides two columns

• In Power BI, to divide the two columns we use the Divide function, Either we can choose the divide function or the (/ – forward slash) divide operator.
• If we are using the divide function then we have to pass the numerator and denominator expressions. Optionally, we can pass a value that represents an alternate result.
• The below represented is the syntax for the divide function,

DIVIDE(<numerator>,<denominator>[,<alternateresult>])

Now we will see a few examples down based on the Divide function in Power BI. Here you can download the excel file used in this example.

How to divide two columns in Power BI

Let us see how we can divide the two columns using the Power BI Divide function.

• Here, we will use the below sample table data to calculate the hours taken per member to complete the assigned project.
• In the below table, you can see that there are different Teams, and each Team has a different Team members count and Total hours to complete the project.
• In this example, we will divide and calculate how many hours have taken Per Member of a Team to complete the project.

• Initially, we will load the data set using the get data option. Then click on the new column from the ribbon to divide the two columns and display the results in the newly created calculated column.
• Then apply the below formula to calculate the hours taken per member to complete the assigned project.

Hours Taken Per Member = DIVIDE(Table1[Hours taken to Complete Project],Table1[Team Members])

Where,

1. Table1 = Excel table name where you are retrieving the data
2. Hours taken to complete the project and Team Members = Column name

• In the data model we can see the calculated column displays the result as shown below:

• We can also display the result in the table visual, by simply dragging and dropping the created calculated column into the column section as displayed below:

This is how to divide the two columns using the Power BI Divide function.

Also, read: Power BI Group By [With 51 real examples]

Power Bi divides two columns in a table

Let us see how we can divide the two columns using the Power BI Divide function and display the calculated value in a Table visual.

• Here, we will use the below sample table data to calculate the hours taken per member to complete the assigned project.
• In the below table, you can see that there are different Teams, and each Team has a different Team members count and Total hours to complete the project. we will divide and calculate how many hours per member of a team has taken to complete the project.

• Load the data into the Power BI desktop using the get data option. Then click on the new column from the ribbon to divide the two columns and display the results in the newly created calculated column.
• Then apply the below formula to calculate the hours taken per member to complete the assigned project.

Hours Taken Per Member = DIVIDE(Students [Hours taken to Complete Project],Students [Team Members])

Where,

1. Students = Table Name
2. Hours took to complete the project and Team Members = Column name

• Then display the result in the table visual, by simply dragging and dropping the created calculated column into the column section as displayed below:

This is how to divide the two columns using the Power BI Divide function and display the calculated value in a Table visual.

Power BI divides two columns to measure

Let us see how to divide two columns using the Power BI measure divide function,

• Here in this example, I will use the table below to calculate the Pending Projects count and Percentage based on the Assigned project count and the Completed project count.
• For this example, we use the Sample table data which is mentioned below:

• First, we will calculate the SUM of the Assigned Project and Completed Project, before that make sure you have Loaded the data using the get data option.
• Click on the new measure from the ribbon to calculate the sum of the assigned project and completed project.
• Apply the below measure to calculate the sum of the assigned project and completed project as shown below:

AssignedProjects = SUM(Table1[Assigned Projects])

CompletedProjects = SUM(Table1[Completed Projects])

Where,

1. Table1 = Excel table name where you are retrieving the data
2. Assigned Projects, Completed Projects = Column names

• To calculate the Pending Projects count, apply the below-mentioned measure formula:

Pending Projects = Table1[AssignedProjects]-Table1[CompletedProjects]

Where,

1. Pending Projects = Measure Name

• Now, select the Matrix visual under the visualizations, In the Rows field drag and drop the Team field and in the values section drag and drop the measures that we calculated for the Assigned Projects, completed projects, and the Pending Projects as shown below:

• We will calculate the Pending Projects Percentage using the divide function, create a new measure from the ribbon, and apply the below-mentioned measure:

Pending Projects Percentage = DIVIDE(Table1[Pending Projects],Table1[AssignedProjects])

• After that change the format to a percentage. Here, in the values section drag and drop the Pending projects percentage field as displayed below:

This is how to divide two columns using the Power BI measure divide function.

Check out: Line Chart in Power BI [Complete Tutorial with 57 Examples]

Power BI divides two columns of percentage

Let us see how to divide two-column and display the result value as a percentage in Power BI,

• Here in this example, I used the below sample data to divide the two columns and display the result in the percentage format.
• So basically, here we will calculate the profit based on the SP and CP value, and we will calculate the profit percentage based on the profit value.

• Now create a new calculated column and apply the below-mentioned formula:

Profit = Products[SP]-Products[CP]

Where,

1. Profit = Measure Name
2. Products = Table Nam
3. SP, CP = Column Names

• In the below screenshot, you can see the new column has been added to the table having the Profit value.

• Now based on the profit value, we will divide and calculate the profit percentage value as follows:
• Basically to calculate the Profit percent formula is Profit value*100/ CP, so now create a new column from the ribbon and apply the below-mentioned formula:

Profit Percentage = Products[Profit]*100/Products[CP]

Where,

1. Profit Percentage = Measure Name

• In the below screenshot, you can see the new column has been added to the table having the Profit Percentage value.

This is how to divide two-column and display the result value as a percentage in Power BI.

Also, read: Power BI date hierarchy [With 21 real examples]

Power BI divides the sum of two columns

Let us see how we can sum up the two columns and divide the sum of one column by the sum of the other column in Power BI.

In this example, I used the below-mentioned sample table data, here we will sum the SP column and CP column separately. Then we will divide the sum of one column by the sum of another column.

• Now create a new measure and apply the below-mentioned formula to sum the SP column and CP column separately:

Where,

1. Sum of SP column, Sum of CP column= Measures Name
2. Products = Table Name
3. SP, CP = Column Names

Sum of SP column = SUM(Products[SP])

Sum of CP Column = SUM(Products[CP])

• Now, select the Matrix visual under the visualizations, In the Rows field drag and drop the Product field and in the values section drag and drop the measures that we calculated sum for the SP column and CP column as shown below:

• We will calculate the sum of two columns using the divide function, create a new measure from the ribbon, and apply the below-mentioned measure:

Sum Result = Products[Sum of SP column]/Products[Sum of CP Column]

Where,

1. Sum Result = Measure Name
2. Products = Table Name
3. Sum of SP column, Sum of CP column= Measures Name

• Here, in the values section drag and drop the Sum Result field as displayed below:

This is how to sum the two columns and divide the sum of one column by the sum of the other column in Power BI.

Also, check out this post: Power bi conditional column [With 17 Useful Examples]

Power BI divides two calculated columns

Let us see how we can divide two calculated columns in Power BI using the divide function,

In this example, I am going to use the below-mentioned sample table data, here we will create two calculated columns, one calculated column displays the gain value. Another calculated column displays the loss value.

• Now create a new column and apply the below-mentioned formula to sum the SP column and CP column to calculate the gain value:

Gain = Products[SP]+Products[CP]

Where,

1. Gain = New calculated column Name
2. Products = Table Name
3. SP, CP = Column Names

• Now create a new column and apply the below-mentioned formula to subtract the SP column and CP column to calculate the loss value:

Loss = Products[CP]-Products[SP]

Where,

• Loss= New calculated column Name
• Products = Table Name
• SP, CP = Column Names

• Then we can divide the gain value and lose value, to divide the two calculated column values, create a new column, and display the result as mentioned below:

Result = Products[Gain]/Products[Loss]

Where,

1. Result= Calculated column Name
2. Products = Table Name
3. Gain and Loss= Calculated column names

• Then select the table or matrix visual and drag and drop the values that you want to display in the Matrix visual.

This is how to divide two calculated columns using the divide function in Power BI.

Read: Power BI Date Slicer [With 41 real examples]

Power BI divides two columns with a filter

Let us see how to divide two columns and filter the context using the divide function and also we will see how to use the filter function to measure Power BI.

We will use the below sample table to calculate the Pending Project percentage and apply a filter based on the team.

First, we will calculate the Assigned projects total and Completed Projects total. Then we will find the Pending Project by using the divide function and at last, we will find the percentage of the Pending Projects by filtering based on the Team.

• Load the data using the get data option in power bi desktop.
• Now we will create a measure that will calculate the Assigned project’s total, so click on the new measure from the ribbon. In the same way, calculate the total of Completed projects.
• Then apply the below-mentioned measure:

AssignedProjects = SUM(Projects[Assigned Projects])

Where,

1. AssignedProjects = Measure Name
2. Projects = Table Name

CompletedProjects = SUM(Projects[Completed Projects])

Where,

1. CompletedProjects = Measure Name
2. Projects = Table Name

• Then Use the divide function, to find the pending project count that creates a measure the apply the following measures.

Pending Projects = Projects[AssignedProjects]-Projects[CompletedProjects]

Pending Projects Percentage = DIVIDE(Projects[Pending Projects],Projects[AssignedProjects])

Where,

• Pending Projects, AssignedProjects = Measures Name
• Projects = Table Name

• From the Pending Projects Percentage, we will filter the percentage of pending projects based on the team, so for this click on the new measure.
• Then write the below measure:

Pending Project Filter = 
    CALCULATE([Pending Projects Percentage], 
        FILTER(Projects,Projects[Team] = "SharePoint Team")
    )

Where,

• Pending Projects Percentage = Measure Name
• Projects = Table Name

• To check the measure, click on the table visual from the visualization pane in power bi.
• In the value field drag and drop the Team, then the measures that we calculated for the Assigned Projects, completed projects, and the Pending Projects as shown below:
• Here we have filtered the pending project percentage only for the SharePoint Team.

This is how to divide two columns and filter the context using the divide function and also we will see how to use the filter function to measure Power BI.

Check: How to create a report in Power BI Desktop? [Excel & SharePoint]

Power BI divides two columns by each row

Let’s see how we can divide two columns by each row and Sum them in Power BI.

In this example, We will use the below sample table to divide row by row using the Divide function, and then we will sum all the divisions using the SUMX function.

For example,  first, we will divide row by row of Team members and Completed Projects: ie. 55/20= 2.75, then we will sum all the divisions:

Load the data and create a measure, and apply the below formula:

Measure 2 = SUMX( Projects, [Completed Projects]/[Team Members])

Where,

1. Projects= Excel table name where you are retrieving the data
2. Completed Projects, Team Members = Column names

• To check the measure, select the table visual from the visualization pane.
• Then in the Value field, drag and drop the Team, Team members, Completed Projects, and Measure2 measure from the field pane as shown below:

This is how we can divide two columns by each row and Sum them in Power BI.

Read: Power BI Add Calculated Column [With Various Examples]

Power BI divides two columns from different tables

Let us see how to use Power BI Measure to divide two columns from different tables.

Here we have two different tables each table has one column in common which is a Country column.

Table:1

Table:2

First, Sum the Sales count in table 1 according to Color, then we will divide by the number of Profit. For example, the sum of Sales for France is 750 and then divided by the Profit amount which is 1100. ie 750/1100= 69.

• Load the data using the get data option, then click on the new measure from the ribbon.
• Then apply the below-mentioned measure:

Result = 
var SumbyCountry = CALCULATE(SUM(Table1[Sales]),ALLEXCEPT('Table1',Table1[Country]))
var Profit = CALCULATE(SUM(Sheet1[Profit]),FILTER('Table1','Table1'[Country]=RELATED('Sheet1'[Country])))
Return DIVIDE(SumbyCountry,Profit)

Where,

1. Table1 and Sheet1 = Table Name
2. SumbyCountry and Profit = Variables Name
3. Sales, Profit, and Country = Column names

• To check the measure, select the table visual from the visualization pane.
• In the value field, drag and drop the product column, sales column, profit column, country column, and Result measure from the field pane as shown below:

This is how to use Power BI Measure to divide two columns from a different table.

Power Bi divides two columns in a matrix

Let us see how we can divide the two columns using the Power BI Divide function and display the calculated value in a Matrix visual.

• In this example, I am going to use the below-mentioned Students Table data to calculate the Percentage of each student.
• Make sure you have Loaded the data using the get data option on the Power BI desktop. Now, click on the New measure from the ribbon and apply the below-mentioned formula:

Percentage = SUMX(Students , (Students [Subject 1]+Students [Subject 2])/200)

Where,

1. Percentage  =Measure Name
2. Students  = Table Name
3. Subject 1 ,Subject 2 = Column names (Number data type)

• In the below screenshot, you can see that it displays the sum value using the SUMX function of multiple columns in Power BI.
• And the divide function divides and calculates the percentage value, Change the data type from General to Percentage data type.
• Then display the result in the Matrix visual, by simply dragging and dropping the created calculated column into the column section as displayed below:

This is how to divide the two columns using the Power BI Divide function and display the calculated value in a Matrix visual.

Check: Power BI Add Calculated Column [With Various Examples]

Power Bi divides two columns with a filter

Let us see how to divide two columns and filter the context using the filter function measure in Power BI.

• In this example, I am going to use the below-mentioned Students Table data to calculate the Percentage of each student and filter the Percentage value based on the student’s name.
• Load the data using the get data option on the Power BI desktop. Now, click on the New measure from the ribbon and apply the below-mentioned formula:

Filter based on Student Name = 
    CALCULATE(Students [Percentage], 
        FILTER(Students ,Students [Student Name] = "Haripriya")
    )

Where,

1. Filter based on Student Name = Measure Name
2. Students = Table Name
3. Student Name = Column Name

• To check the measure, click on the table visual from the visualization pane in power bi.
• In the value field drag and drop the Student Name, then the measures that we calculated for the total marks and Percentage as shown below:
• Here we have filtered the percentage only for the particular student name called Haripriya.

This is how to divide two columns and filter the context using the filter function measure in Power BI.

Read out: Power BI Matrix Multiple Column

Divides two counts in Power BI

Let us see how we can Divide two distinct counts in Power BI,

In this example, we will calculate the two distinct count values for the student name and the subject mark1. Later, we will divide the two distinct count values.

• Load the data using the get data option on the Power BI desktop. Now, click on the New measure from the ribbon and apply the below-mentioned formulae to calculate the two distinct values and divide them:

no. of students = DISTINCTCOUNT(Students [Student Name])

Where,

1. no. of students = Measure Name
2. Students  = Table Name
3. Student Name = Column Name

Distinct Marks = DISTINCTCOUNT(Students [Subject 1])

Where,

1. Distinct Marks= Measure Name
2. Students = Table Name
3. Subject 1 = Column Name

Divides two counts = DIVIDE([no. of students],[Distinct Marks])

Where,

1. Divides two counts = Measure Name
2. no. of students & Distinct Marks = already calculated distinct count value measures

• To check the measure, click on the table visual from the visualization pane in power bi.
• In the value field drag and drop the Student Name, then the measures that we calculated for the Distinct count values and divided values.

This is how to Divide two distinct counts in Power BI.

Power Bi divides one measure by another

Let us see how we can divide one measure by another in Power BI,

• in this example, I will use the table below to calculate the Pending Projects count and Percentage based on the Assigned project count and the Completed project count.
• For this example, we use the Sample table data which is mentioned below:

• First, we will calculate the SUM of the Assigned Project and Completed Project, before that make sure you have Loaded the data to the Power BI desktop using the get data option.
• Click on the new measure from the ribbon to calculate the sum of the assigned project and completed project.
• Apply the below measure to calculate the sum of the assigned project and completed project as shown below:

AssignedProjects = SUM(Projects[Assigned Projects])

Where,

1. AssignedProjects= Measure Name
2. Projects = Table Name
3. AssignedProjects = Column Name

CompletedProjects = SUM(Projects[Completed Projects])

Where,

1. CompletedProjects= Measure Name
2. Projects = Table Name
3. CompletedProjects = Column Name

Pending Projects = Projects[AssignedProjects]-Projects[CompletedProjects]

Where,

1. Pending Projects= Measure Name
2. Projects = Table Name
3. AssignedProjects & CompletedProjects = already created measures

Pending Projects Percentage = DIVIDE(Projects[Pending Projects],Projects[AssignedProjects])

Where,

1. Pending Projects Percentage = Measure Name
2. Projects = Table Name
3. AssignedProjects & Pending Projects = already created measures

This is how to divide one measure by another in Power BI.

Also, you may like some below Power BI Tutorials:

• Stacked Bar Chart in Power BI
• Power BI Bookmarks
• Power BI filter between two dates [With 15+ Examples]
• Power BI Sum Multiple columns
• Clustered Column Chart in Power BI
• Power bi change color based on value
• Power BI calculated column [With 71 Useful Examples]
• Power BI sync slicers [With 15 useful examples]

In this Power BI Tutorial, we learned about how to divide two columns using the divide function in Power BI with different examples, and also covered the below-mentioned topics :

• Power BI DAX divides two columns
• How to divide two columns in Power BI
• Power BI divides two columns to measure
• Power BI divides two columns of percentage
• Power BI divides the sum of two columns
• Power BI divides two calculated columns
• Power BI divides two columns with a filter
• Power BI divides two columns by each row
• Power BI divides two columns from different tables
• Power Bi divides two columns in a table
• Power Bi divides two columns in a matrix
• Power Bi divides two columns with a filter
• Divides two counts in Power BI
• Power Bi divides one measure by another