Menu
Stuff by Yuki
  • Home
  • Data Engineering
    • Python
  • Business Intelligence
    • Power BI
    • Tableau
  • Perspectives
  • About
  • Contact
Stuff by Yuki

Calculate the Max Sales Amount for Product

Posted on March 2, 2023March 2, 2023

Setup

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

The data model looks like this:

Code

Copy Copied Use a different Browser

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

The result looks like this:

Validating the output:

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

Copy Copied Use a different Browser

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].

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • Where I’m Headed in the Next 5 Years
  • Open-Source vs Vendor Data Tools
  • Developing the Habit of Writing
  • How to Inspect and Optimize Query Plans in Python Polars
  • Learn Python Polars with Polars Cookbook

Popular Posts

  • A Running Total Calculation with Quick Measure in Power BI
  • A Complete Guide to Git Integration in Power BI
  • How To Copy And Paste Report Page in Power BI
  • Handling Missing Values in Polars
  • How to Convert String to Date or Datetime in Polars

connect with me

  • LinkedIn
  • Twitter
  • Github
  • Website

Search Articles

©2025 Stuff by Yuki | Powered by SuperbThemes