Exercise - Add a calculated column and measure in Power BI Desktop using DAX

  • 5 minutes

This exercise is written to use the Cronus sample company from the US version of Business Central. You might need to make adjustments to the steps if you use the sample company from your country or region.

Scenario

You've imported the item query in Power BI Desktop. The next step is to create a measure, using a DAX expression, that calculates the availability per item, based upon the inventory and the quantity on sales and purchases. This calculation can be created as a calculated column in the ItemAvailability table. Next you'll also create a measure that calculated the number of items that currently have a negative availability.

Steps

  1. In Power BI Desktop, in the Data view, select New column under Table tools.

    Screenshot of the Add a calculated column feature.

  2. In the formula enter the following DAX expression:

    Availability = ItemAvailability[Inventory] + ItemAvailability[QtyOnPurchOrder] - ItemAvailability[QtyOnSalesOrder]

  3. Press Enter. The new column named Availability should be added to the table.

    Screenshot of the Column availability view.

  4. In Power BI Desktop, in the Data view, select New measure under Table tools.

    Screenshot of the Power BI Desktop ribbon with New measure.

  5. In the formula enter the following DAX expression:

    mNoOfItemsWithNegAvailability = CALCULATE(DISTINCTCOUNT(ItemAvailability[ItemNo]);ItemAvailability[Availability]<0)

  6. Press Enter. The new measure names mNoOfItemsWithNegAvailability is now added to the data model.

    Screenshot of the Measure created in the data model.