You can write a DAX formula to add a measure to any table in your model. A measure formula must return a scalar or single value.

Measures within the model don't hold data; instead, they come into play during queries to provide condensed summaries of model information. Furthermore, measures cannot directly refer to a table or column; they need to pass the table or column through a function to generate a summarization.

A basic measure aggregates values from a single column, akin to the automatic functionality of implicit measures.

In the upcoming instance, you'll incorporate a measure into the Sales table. Navigate to the Fields pane, choose the Sales table, and, within the Calculations group inside the Table Tools contextual ribbon, opt for the creation of a New measure.

In the formula bar, enter the following measure definition and then press Enter.

DAXCopy

Revenue =
SUM(Sales[Sales Amount])

The measure definition adds the Revenue measure to the Sales table. It uses the SUM DAX function to sum the values of the Sales Amount column.

On the Measure tools contextual ribbon, inside the Formatting group, set the decimal places to 2.

Now, add the Revenue measure to the matrix visual. Notice that it produces the same result as the Sales Amount implicit measure.

In the matrix visual, remove Sales Amount and Sum of Unit Price.

Next, you will create more measures. Create the Cost measure by using the following measure definition, and then set the format with two decimal places.

Cost =
SUM(Sales[Total Product Cost])

Create the Profit measure, and then set the format with two decimal places.

DAXCopy

Profit =
SUM(Sales[Profit Amount])

Notice that the Profit Amount column is a calculated column. This topic will be discussed later in this module.

Next, create the Quantity measure and format it as a whole number with the thousands separator.

DAXCopy

Quantity =
SUM(Sales[Order Quantity])

Create three unit price measures and then set the format of each with two decimal places. Notice the different DAX aggregation functions that are used: MIN, MAX, and AVERAGE.

DAXCopy

Minimum Price =
MIN(Sales[Unit Price])

DAXCopy

Maximum Price =
MAX(Sales[Unit Price])

DAXCopy

Average Price =
AVERAGE(Sales[Unit Price])

Now, hide the Unit Price column, which results in report authors losing their ability to summarize the column except by using your measures.

Next, create the following two measures, which count the number of orders and order lines. Format both measures with zero decimal places.

DAXCopy

Order Line Count =
COUNT(Sales[SalesOrderLineKey])

DAXCopy

Order Count =
DISTINCTCOUNT('Sales Order'[Sales Order])

The COUNT DAX function counts the number of non-BLANK values in a column, while the DISTINCTCOUNT DAX function counts the number of distinct values in a column. Because an order can have one or more order lines, the Sales Order column will have duplicate values. A distinct count of values in this column will correctly count the number of orders.

Alternatively, you can choose the better way to write the Order Line Count measure. Instead of counting values in a column, it's semantically clearer to use the COUNTROWS DAX function. Unlike the previously introduced aggregation functions, which aggregate column values, the COUNTROWS function counts the number of rows for a table.

Modify the Order Line Count measure formula you created above to the following parameters:

DAXCopy

Order Line Count =
COUNTROWS(Sales)

Add each of the measures to the matrix visual.

All measures that you've created are considered simple measures because they aggregate a single column or single table.

SWETA SARANGI