AVERAGEX Function in DAX

In DAX, the AVERAGEX function is used to compute the average of an expression evaluated for each row in a table. In your case, we can use the AVERAGEX function to calculate the average profit for each product sold. The syntax would be as follows:

AverageProfit = AVERAGEX('Sales Table', 'Sales Table'[Profit])

In this DAX expression, 'Sales Table' is the name of the table where the average calculation is performed, and 'Sales Table'[Profit] is the expression for which to compute the average.

This code will yield the average profit of the sales by taking the profit for each row in the 'Sales Table' and then computing the average.

To calculate average profit per product, you can use a similar formula, but you first need to iterate over a distinct list of products rather than the whole 'Sales Table'. That way, the averaged will be done per product instead:

AverageProfitPerProduct = AVERAGEX(
  VALUES('Sales Table'[Product]), 
  CALCULATE(SUM('Sales Table'[Profit]))
)

Here we are using a combination of the AVERAGEX, VALUES, and SUM functions.

• We obtain a distinct list of products with VALUES('Sales Table'[Product]).
• Then for each product, we are using CALCULATE(SUM('Sales Table'[Profit])) to summarize the profit.
• Finally, we average these totals.

Remember that in DAX, the iteration functions like AVERAGEX process each row context in isolation, and you often need to use CALCULATE to modify the filter context. If you want to learn more about these concepts and functions, consider exploring courses offered on the Enterprise DNA platform.