Calculate the Max Sales Amount for Product

Setup

I use the Contoso data model. You can access it dax.do or download pbix here.

The data model looks like this:

Code

MAXX ( 
    VALUES ( 'Product'[ProductKey] ), 
    [Sales Amount] 
)

The result looks like this:

Validating the output:

Here’s the full code I used in dax.do:

DEFINE
    MEASURE Sales[Product Max Sales Amount] =
        MAXX ( VALUES ( 'Product'[ProductKey] ), [Sales Amount] )

EVALUATE
ROW ( "Product Max Sales Amount", [Product Max Sales Amount] )

EVALUATE
SUMMARIZECOLUMNS ( 'Product'[Product Name], "Sales", [Sales Amount] )
ORDER BY [Sales] DESC

Explanation

VALUES() gets unique products from product table. You could just reference the table, but I use VALUES() to make sure I’m iterating unique values. I’ve seen cases where DAX engine was able to optimize better with VALUES() compared to without it.

MAXX() iterates each product and calculates the sum of sales, and take the max of those calculated sales values. If you’re used to SQL then it’s like creating a CTE that calculates the sum of sales at the product level and apply MAX over it.

Also, it’s worth noting that context transition is in place where we have [Sales Amount].