Microsoft Power BI Measures

    Power BI desktop offers a wide range of functionalities to create reports and insights into data easily. But still, there might be some areas or points in a report where we feel that we cannot represent what we want with the data that we have. For such cases, we can create our own measures in Power BI.

    Usually, measures are used in aggregation calculations such as sum, average, minimum value, maximum value, counts or more advanced level calculations made using DAX formula. So, in other words, measures are like formulas that are calculated. Then it produces a result specific to the purpose and values it is used for.

Creating Measures:

Before we start creating a new measure, let's open an existing Power BI report named as Profit Margin in Power BI Desktop.

The report consists of three tables named as Certificate, Films and Genre.

In the model view, the tables are already connected to manage relationship between the them, where Films table is the master table here.

Now, the requirement is to create a fourth table to hold 3 measures and to display them in a table visual.

The measures to be created are:

1.  Average Box Office takings in million dollar as its data type.
2.  Average Budget spend in million dollar as its data type.
3.  Average Profit Margin as percentage as its data type.

So, before we start creating these measures, we need to create a table by using DAX which includes same content of the table Certificate.

For that, go to Modeling ribbon > New table. A formula bar will appear and in that will enter:

Certificate_Measure = Certificate 

This will give the reflection of the table Certificate with its content.

In the Field pane, Certificate_Measure table can be seen with a blue highlight on the icon.

To create measure for Certificate_Measure table, right click on it and we get an option for New Measure to select.

Formula bar is displayed, where we have to enter the DAX formula to create measure for Average Box Office in million dollars.

Average Box Office ($m) = FORMAT(AVERAGE(Films[BoxOfficeDollars]), "#,,.00")

Now, similarly let's create the second measure, Average Budget in million dollars, by right clicking the table Certificate_Measure.

In the formula bar enter the DAX formula as:

Average Budget ($m) =FORMAT(AVERAGE(Films[BudgetDollars]), "#,,.00")

Now let's add these two newly created measures to the Table Visual.

Select table visual from Visualization pane, a blank visual is displayed on the canvas.

So to add variable into the table we will select Certificate name, Average Box Office

($m) and Average Budget($m) from the field pane.

Next to add Average Profit Margin to the table we need to create the measure for it using the DAX measure as:

Average profit margin = AVERAGEX(Films,DIVIDE(Films[BoxOfficeDollars]-

Films[BudgetDollars], Films[BoxOfficeDollars], 0 ))

Select this newly created measure from Field pane and add it to the table, also

change its data type to % from Measure tools > Format.

The required table will finally look like this: