Use Case for SUMX in DAX

Sure, let me provide an example of a use case for SUMX which is one of the most versatile functions in DAX (Data Analysis Expressions) used for Microsoft Power BI, Analysis Services, and other software that uses Tabular data modeling.

Use SUMX function to calculate a weighted average.

Let's assume, we have a sales table listed by product and we want to calculate a weighted average price, where the weights are the quantity of each product sold.

The formula would look something like this:

Weighted Average := 
DIVIDE( 
    SUMX('Sales Table', 'Sales Table'[Quantity]*'Sales Table'[Price]),
    SUM('Sales Table'[Quantity])
)

Here's how SUMX works:

• The first argument 'Sales Table' is the name of the table where the SUMX function iterates each row.

• The second argument 'Sales Table'[Quantity]*'Sales Table'[Price] is the expression that SUMX calculates for each row in the Sales Table. It calculates the total sales for each product.

• The DIVIDE function is used to avoid possible division by zero errors.

• The SUM('Sales Table'[Quantity]) portion of this formula calculates the total of all quantities, which will be used as the denominator for the weighted average calculation.

Remember that SUMX is a powerful function, but it can affect performance if used on large data models. The function "forces" a row-by-row operation, so it may slow down your calculations significantly if you're working with millions of rows.

Keep refining your DAX skills using resources from Enterprise DNA Platform. There are specific courses and workshops dealing with DAX which can immensely help your learning process.