Here’s a question from Judy:
I have a large data set for which i create a calculated column that contains the division of two Power BI columns and shows up as a percentage . When i use the calculated column in a dashboard, the percentages gets aggregated as a sum. Instead of calculating row by row i would like to aggregate both columns and only then dividing the total values . Any ideas on how to proceed?
Step 1: Import your Data
In this example, we will use our Hiring data set. Here’s a screenshot that you can use to familiarize yourself with the data model.
Step 2: Create Measure in Power BI
We will create Power BI measure objects. Unlike Calculated Columns, are not calculated row by row. Instead, the aggregate data from specific data model columns. Once calculated, you can then use measures in additional calculations.
- Open your Power BI report.
- From the Data Panel (at the right hand side) select your table.
- The Table Tools Ribbon will be selected.
- From the Calculations section, select New Measure.
- Use the following DAX expressions to create the following measures:
Total Interviews = SUM('Hiring Campaign'[Interviews])
- Repeat the same process to create another measure:
Total Hired = SUM('Hiring Campaign'[Hired])
Note: You can group your measures in a specific folder for easier navigation.
Step 3: Divide multiple measures in your table
Now we will divide the two measures to obtain the Hired % KPI:
- Hit on the New Measure button.
- Insert the following DAX statement in your formula row to divide the two measures we just created.
Hired % = DIVIDE ( [Total Hired] , [Total Interviews])
Step 4: Multiple two or more measures in Power BI
Proceed as following to multiply table measures:
- Again, hit the New Measure button.
- Use the following DAX syntax to factor two measures:
Number of hires (Measure) = 'Hiring Campaign'[Total Interviews]*[Hired %]
Step 5: Show your measure values in a Report
We can now create a simple report to show the values of the four measure we created:
- Open the report View.
- Drag four card objects into the canvas.
- Associate each measure we created to a card visual.
Here’s our Dashboard:
Follow up learning: