Using DAX Measures that calculate by row rather than a summary, in Microsoft Power BI

 

Math

In this Video, I’ll create a DAX (Data Analysis eXpression) Measure.  This measure will be used to calculate the total sales price.  Check out this video to see how to create a measure that performs “Aggregation with Iteration”; which is a fancy way of saying DAX needs to calculate the total sales price for each row, then sum it together.  Look at the two examples below and you’ll see why.

Sold Quantity Per Item Price (Unit Price)
5 T-Shirts $5 per shirt
10 Stickers $1 per sticker

Which is correct to calculate the Total Sales?

15 (sum of the quantity of all items sold) * $6 (sum of Unit Price) = $90 Total Sales

OR

(5 T-Shirts * $5) + (10 Stickers * $1) = $35 Total Sales

DAX can be used in Microsoft Power BI, Microsoft PowerPivot, and MDX.  It’s a relatively easy way to model your data.

Today’s DAX Expression:

Extended Price := SUMX(‘Sales Line Items’, ‘Sales Line Items'[Qty] * ‘Sales Line Items'[Unit Price])

Leave a Reply