Menu
Stuff by Yuki
  • Home
  • Python
  • Power BI
  • Tableau
  • Community
    • Makeover Monday
    • Workout Wednesday
  • 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

  • What are Power BI Dataflows?
  • Calculate the Max Sales Amount for Product
  • Convert DataFrame to Series in Polars
  • Pandas vs Polars – Speed Comparison
  • Polars with DuckDB – Using SQL in Polars

Popular Posts

  • A Running Total Calculation with Quick Measure in Power BI
  • How To Copy And Paste Report Page in Power BI
  • Year-Over-Year Calculation: Time Intelligence in Power BI
  • How to Fill Dates Between Start Date and End Date in Power BI (Power Query)
  • Network Visualizations in Python

connect with me

  • LinkedIn
  • Twitter
  • Github
©2023 Stuff by Yuki | Powered by SuperbThemes & WordPress