Power bi measure divide + 9 examples

In this Power Bi tutorial, we will discuss the different examples of the Power bi measure divide. And also we will discuss the below points:

• Power bi measure divide one column by another
• Power bi measure divide two columns
• Power bi measure divide with filter
• Power bi measure divide by zero
• Power bi measure divide by grand total
• Power bi measure divide by row by row
• Power bi measure divide two columns from different table
• Power bi measure divide column by number
• Calculate a row in a column divided by the total of that column

Power bi measure divide

In Power bi, to divide the numerator by a denominator, we can choose to use the DIvide function, and / operator

If you use the divide function then you must pass the numerator and denominator.

The syntax for divide function:

Divide(<numerator>,< denominator>, <alternative result>

Read Power Bi Relationship Functions

Power bi measure divide one column by another

Here we will see how to divide one column by another using the power bi measure divide function.

We will use the below sample table to find the click-through rate (CTR) by using the formula Clicks/ Impressions.

We will first calculate the sum of click and sum of impression and then we will calculate the Sum of clicks/ sum of impression using a Power BI measure.

• Load the data using get data in power bi dektop.
• Click on the new measure from the ribbon to calculate the sum of click.
• Then write the below measure:

Total click = SUM('Table'[Click])

• And next we will find the sum of impression, so click on the new measure.
• Then write the below measure:

Total impression = SUM('Table'[Impression])

• Now we will calculate the click through rate (CTR), click on the new measure.
• Then write the below measure:

CTR = DIVIDE([Total click],[Total impression])

• Now click on the measure to change the format decimat to percentage.
• Then measure tool ribbon will open, in the format section change the format to percentage.

• Now to check the measure, select the card visual from the visualization pane.
• In the fields, drag and drop the CTR measure from the field pane.

Read Power bi measure switch statement with examples

Power bi measure divide two columns

Here we will how to divide two columns using power bi measure divide function.

We will use the below sample table to find the percentage of not completed task.

First, we will calculate the total task and total task completed then we will find the task not completed. And at last, we will find the percentage of task not completed, by dividing task not completed with total tasks.

• Load the data using get data.
• Click on the new measure from the ribbon to calculate the sum of task.
• Then write the below measure:

Total tasks = SUM(TaskTable[Tasks])

• Now we will calculate the sum of completed task, click on the new measure from the ribbon.
• Then write the below measure:

TotalTaskCompleted = SUM(TaskTable[Completed])

• Now we will find the task is not completed, so click on the new measure from the ribbon.
• Then write the below measure:

Not completed = [Total tasks] -[TotalTaskCompleted]

• At last we will find the percentage of not completed task, so click on the new measure from the ribbon.
• Then write the new measure is:

Not completed percentage = DIVIDE([Not completed],[Total tasks],0)

• Now to check the measure, select the table visual from the visualization pane.
• In the value field, drag and drop the task name column, Tasks, Task completed measure, Task not completed measure and percentage of task not completed.

Read Countif function in Power BI Measure

Power bi measure divide with filter

Here we will see how to divide two columns and filter the context using divide function and a filter function in measure.

We will use the below sample table to calculate the Hit rate percentage without SIS store type.

First, we will calculate the total traffic and total sales transaction. Then we will find the hit rate by using the divide function and at last, we will find the percentage of the hit rate by filtering the store type.

• Load the data using get data in power bi desktop.
• Now we will create a measure which will calculate the total traffic, so click on the new measure from the ribbon.
• Then write the below measure:

TotalTraffic = Sum(Sales[Traffic])

• We will find the total salte transaction, so click on the new measure from the ribbon.
• Then write the below measure:

TotalTraffic = Sum(Sales[Traffic])

• We will find the hit rate, so for this click on the new measure.
• Then write the below power bi measure:

Hit Rate = DIVIDE([Total Sales Transactions],[TotalTraffic],0)

• From the Hit rate we will filtered out the Store type SIS then we will show the percentage of hit rate, so for this click on the new measure.
• Then write the below measure:

HitRateFilter = 
    CALCULATE([Hit Rate], 
        FILTER(Sales, Sales[Store Type] <> "SIS")
    )

• Click on the Hit rate filter measure from the field pane, Measure tools will open in the ribbon.
• From the formatting section, change the format from general to percentage.

• Now to check the measure, click on the table visual from the visualization pane in power bi.
• In the value field drag and drop the short month column, store type column, Traffic column, hit rate measure and hit rate filer measure

Read How to create a Measure based on Slicer in Power BI

Power bi measure divide by zero

Here we will see how to handle divide by zero error using divide function in measure.

We will use the below sample table, whereas col1 as the numerator and col2 as the denominator. And in these columns, some of the value is either null or zero.

By using the standard division, we will see what error we get and then we will use the divide function to solve the error.

• Load the data using get data.
• Click on the new measure from the ribbon.
• Then write the below measure:

StandardDivision = SUM(Table2[Column1])/SUM(Table2[Column2])

• Click on the measure in the field pane, then in the measure tools change the format of measure to percentage.

Now, In the below screenshot you can see, when the numerator is zero it is showing 0%.

But when denominator is zero or blank it is showing infinity and when the numerator is blank it is showing the result as blank.

• Now we will use divide function to solve this error, instead of infinity it will show 0%.
• Click on the new measure from the ribbon.
• Then write the below measure:

Measure = DIVIDE(SUM(Table2[Column1]), SUM(Table2[Column2]),0)

In the below screenshot, you can see instead of infinity, we are getting 0 as an output, but in the last row we are still getting blank, where the numerator is blank.

We can solve this by modifying the above measure by using switch() and IsBlank().

• Click on that Measure to open.
• Then rewrite the above measure:

Measure = SWITCH(TRUE(),ISBLANK(DIVIDE(SUM(Table2[Column1]), SUM(Table2[Column2]),0)),0,DIVIDE(SUM(Table2[Column1]), SUM(Table2[Column2]),0))

Now, in the below screenshot you can see there is no error and the output is zero.

Read Power BI Measure SUM

Power bi measure divide by grand total

Here we will see how to calculate grand total percentage by using the divide() in measure.

We will use the financial sample data to calculate the grand total percentage of sales based on product. You can download the sample data from here.

• load the data using get data.
• Click on the new measure from the ribbon to calculate the grand total percentage of sales.
• Then write the below measure:

% of Grand Total = 
DIVIDE (
    SUM ( financials[ Sales] ),
    CALCULATE ( SUM ( 'financials'[ Sales] ), ALL ( 'financials'[Product] ) )
)

• Now to check the measure, select the table visual from the visualization pane.
• In the value field, drag and drop the product column, sales column, and % of grand total measure from the field pane.

Read Power bi measure by category

Power bi measure divide by row by row

Here we will see how to divide row by row and then we will sum all the division rows by using a measure.

We will use the below sample table to divide row by row and then we will sum all the divisions using sumx function.

For example, first we will divide row by row of total task and total task completed: 10/5 = 2.0; 11/3 = 3.67, 14/6 = 2.33, 25/25 = 1, 12/9 = 1.33, 18/9= 2.0, 20/15= 1.33

Then we will sum all the division:

2.0+3.63+2.33+1.0+1.33+2.0+1.33= 13.67

• Load the data using get data
• Click on the New measure from the ribbon.
• Then write the below measure

Measure 2 = SUMX( TaskTable, [Total tasks]/[TotalTaskCompleted])

• Now to check the measure, select the table visual from the visualization pane.
• Then in the value field, drag and drop the task name column, completed column, tasks column, and Measure2 measure from the field pane.

Read Power bi measure subtract

Power bi measure divide two columns from different table

Here we will see how to use power bi measure divide two columns from a different table.

Here we have two tables that each table has one column common i.e color common.

Table 1

Table 2

First, we will sum the size in table 1 according to color, then we will divide by the number of staff. For example sum of size for blue color is 246 and then divided by 6 which is 246/6=41

• Load the data using get data.
• Click on the new measure from the ribbon.
• Then write the below measure:

Result = 
var sumbycolor = CALCULATE(SUM('Table 3'[Size]),ALLEXCEPT('Table 3','Table 3'[Color]))
var staffno = CALCULATE(SUM('Table 4'[Staff]),FILTER('Table 3','Table 3'[Color]=RELATED('Table 4'[Color ])))
Return DIVIDE(sumbycolor,staffno)

• Now to check the measure, select the table visual from the visualization pane.
• In the value field, drag and drop the color column, staff column, size column, and result measure from the field pane.

Read Power BI Measure IF with Examples

Power bi measure divide column by number

Here we will see how to divide column by number by using the measure in power bi.

We will use the below sample table to sum the score according to the group, then divide each score by sum of each group.

• Load the data using get data.
• Click on the New measure from the ribbon.
• Then write the below measure:

Measure 3 = 
DIVIDE (
    SUM ( 'Table 5'[Score] ),
    CALCULATE ( SUM ( 'Table 5'[Score] ), ALLSELECTED ( 'Table 5'[Category] ) )
)

• To check the measure, select the table visual from the visualization pane.
• Then in the value field drag and drop the group column, category column, score column and measure 3 from the field pane.

Calculate a row in a column divided by the total of that column

Here we will see how to divide a row value of a column by the total of that column.

We will use the below sample to calculate a row in a column divided by the total of that column.

• Load the data using get data.
• Then click on New measure from the ribbon.
• Then Write the below measure:

Measure = Var denom= CALCULATE(Sum('Table'[Values]),ALL('Table'))
Return SUM('Table'[Values])/denom

• To check the measure, select the table visual from the visualization pane.
• Then drag and drop the column1, value column and measure from the field pane.

You may also like the following Power Bi tutorials:

• Microsoft Power BI KPI Visual
• Microsoft Power BI Stacked Column Chart
• How to use Microsoft Power BI Scatter Chart
• Microsoft Power BI Combo Chart
• Microsoft Power bi report vs dashboard
• Power BI Measure multiply with examples

In this power bi tutorial, we discussed the different types of examples in the Power Bi measure divide. These examples are listed below:

• Power bi measure divide one column by another
• Power bi measure divide two columns
• Power bi measure divide with filter
• Power bi measure divide by zero
• Power bi measure divide by grand total
• Power bi measure divide by row by row
• Power bi measure divide two columns from different table
• Power bi measure divide column by number