Create measures using DAX in Power BI

Tip: All calculations can be copied from the D:\PL300\Demo\Resources\Snippets-Demo-05.txt file.

Create a calculated table

  1. Create a calculated table by using the following expression:
Date = CALENDARAUTO()
  1. Switch to Data view, and review the table, which comprises a single date column.

Create calculated columns

  1. Add a calculated column to the Date table:
Year = "CY" & YEAR('Date'[Date])
  1. Add an additional calculated column to the Date table:
Month = FORMAT('Date'[Date], "YYYY-MM")

  1. In Model view, create a relationship by dragging the Date table Date column to the Sales table OrderDate column.

  2. Hide the Sales table OrderDate column.

  3. In the Date table, create the Calendar hierarchy, with Year and Month levels.

  4. In Report view, mark the Date table as a date table using the Date column.

  5. In the matrix visual, remove the Products hierarchy, and then replace it with the Calendar hierarchy.

  6. Add a calculated column to the Sales table:

Cost = 'Sales'[Quantity] * RELATED('Product'[Cost])
  1. Format the Cost column to two decimal places.

Create a Quick Measure

  1. Add a quick measure to the Sales table, subtracting the Cost column from Profit column.

  2. Rename the measure as Profit.

  3. Explain that the measure does not store data in the model.

Create regular measures

  1. Add a measure to the Sales table:
Profit Margin = DIVIDE([Profit], SUM('Sales'[Sales]))

  1. Format the Profit Margin column as a percentage.

  2. Add another measure to the Sales table:

Sales YTD = TOTALYTD(SUM('Sales'[Sales]), 'Date'[Date])
  1. Format the Sales YTD column to two decimal places.

Validate the calculations with the matrix visual

  1. Add the Cost, Profit, Profit Margin, and Sales YTD fields to the matrix visual.

  2. Save the Power BI Desktop file.

  3. Leave the Power BI Desktop file open for a later demo.