Calculating Average Profits for Each Product Sold

To calculate the average profits for each product sold, you can make use of the AVERAGEX function. The AVERAGEX function in DAX allows you to evaluate and average an expression for each row in a given table.

Step 1: Firstly, identify the base table that you'll use for iterating over. In this case, it is 'Sales Table'.

Step 2: Next, define the expression you want to average. Since you're looking to calculate average profits, the expression will simply be the 'Profit' column.

Here is the DAX expression that follows these steps:

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

This DAX formula will iterate over each row in the 'Sales Table', evaluate the 'Profit' value for that row, sum up all these values, and divide by the number of rows to calculate the average profit.

But if you want to calculate the average profit per product, you should iterate over a product table (let's call it 'Product Table') which provides a unique list of products, then use RELATED function to get the 'Profit' from the 'Sales Table'. Here is the amended expression:

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

Make sure that 'Product Table' and 'Sales Table' have proper relationships set in the data model.

This formula iterates over 'Product Table'. For each product, it computes the total profit associated with that product using the CALCULATE and SUM functions, then AVERAGEX function averages these total profits.

It's important to note that the AVERAGEX function makes a row context over the specified table. You can use any other DAX expression as its second argument, as long as it returns a single scalar value.