topic Calling DAX Masters - Price Realization Over Time in DAX Commands and Tips

Calling DAX Masters - Price Realization Over Time

n8ball — Mon, 20 Mar 2023 20:11:59 GMT

I've been working on this measure for a long time. Trying to find the best performance. Currently it is not good once you start filtering by Months. Lots of nested iterators and high materialization. I'm starting to think I'm going to have to model this differently.

I recreated it in Contoso so you have a model to work with. I need the logic in place that eliminates any rows that don't have positive sales or quantity in either period and in my production model I also have to remove return rows. I also need to evaluate price realization by channel as there can be different discount levels and strategies in different channels for the same product. Thanks in advance for any help or guidance you can spare. Model Download

Here is the measure:

Price Realization by Channel = VAR YTDTable = CALCULATETABLE( DATESYTD( 'Calendar'[DateKey] ), 'Calendar'[DatesInThePast] = TRUE ) VAR PYTDTable = CALCULATETABLE( SAMEPERIODLASTYEAR( YTDTable ), 'Calendar'[DatesInThePast] = TRUE ) VAR MaterialTable = CALCULATETABLE( ADDCOLUMNS( SUMMARIZE( 'Sales', 'Product'[ProductKey], 'Channel'[Channel]), "@ValueYTD", CALCULATE( SUM('Sales'[SalesAmount]), YTDTable ), "@QtyYTD", CALCULATE( SUM('Sales'[SalesQuantity]), YTDTable ), "@ValuePYTD", CALCULATE( SUM('Sales'[SalesAmount]), PYTDTable ), "@QtyPYTD", CALCULATE( SUM('Sales'[SalesQuantity]), PYTDTable ) ), 'Sales'[ReturnAmount] = 0 ) VAR MaterialTable_Filtered = FILTER( MaterialTable, [@ValueYTD] > 0 && [@QtyYTD] > 0 && [@ValuePYTD] > 0 && [@QtyPYTD] > 0 ) RETURN SUMX( MaterialTable_Filtered, ( DIVIDE( [@ValueYTD], [@QtyYTD] ) - DIVIDE( [@ValuePYTD], [@QtyPYTD] ) ) * [@QtyYTD] )

Another approach I tried was SUMX over a Filter and Sumerize. Reduced materialization but required more storage engine queries and as a result performed worse.

Price Realization by Channel 2 = VAR YTDTable = CALCULATETABLE ( DATESYTD ( 'Calendar'[DateKey] ), 'Calendar'[DatesInThePast] = TRUE ) VAR PYTDTable = CALCULATETABLE ( SAMEPERIODLASTYEAR ( YTDTable ), 'Calendar'[DatesInThePast] = TRUE ) RETURN CALCULATE ( SUMX ( FILTER ( SUMMARIZE ( 'Sales', 'Product'[ProductKey], 'Channel'[Channel] ), CALCULATE ( SUM ( 'Sales'[SalesAmount] ), YTDTable ) > 0 && CALCULATE ( SUM ( 'Sales'[SalesQuantity] ), YTDTable ) > 0 && CALCULATE ( SUM ( 'Sales'[SalesAmount] ), PYTDTable ) > 0 && CALCULATE ( SUM ( 'Sales'[SalesQuantity] ), PYTDTable ) > 0 ), ( ( ( CALCULATE ( SUM ( 'Sales'[SalesAmount] ), YTDTable ) / CALCULATE ( SUM ( 'Sales'[SalesQuantity] ), YTDTable ) ) ) - ( ( CALCULATE ( SUM ( 'Sales'[SalesAmount] ), PYTDTable ) / CALCULATE ( SUM ( 'Sales'[SalesQuantity] ), PYTDTable ) ) ) ) * CALCULATE ( SUM ( 'Sales'[SalesQuantity] ), YTDTable ) ), 'Sales'[ReturnAmount] = 0 )

Re: Calling DAX Masters - Price Realization Over Time

puneetvijwani — Wed, 22 Mar 2023 00:06:57 GMT

n8ball Will Suggest you to change the model and try Pre loading the data as AggregatedSales table by either on date or by month level granularity
For eg below

AggregatedSales = SUMMARIZE( FILTER('Sales', 'Sales'[ReturnAmount] = 0), 'Product'[ProductKey], 'Channel'[Channel], 'Calendar'[DateKey], "SalesAmount", SUM('Sales'[SalesAmount]), "SalesQuantity", SUM('Sales'[SalesQuantity]) )

and then create DAX

or you can materlize and filter your table which you are doing as pre calculation step in cleansing process and loadit as an Aggregate table , It shall reduce lots of complexity

Foy dynamic time intelligence you can use something as below article by phil seamark

https://dax.tips/2019/10/09/row-based-time-intelligence/

------------------------------------------------------------------------------------------

Linkedn :-https://www.linkedin.com/in/puneet-v-376470135/
Youtube :-https://www.youtube.com/@datatrends101

Re: Calling DAX Masters - Price Realization Over Time

n8ball — Wed, 22 Mar 2023 18:40:08 GMT

Yup. I will have to try to generate a price table. The only issue there is you loose the dynamic nature of the measure. I think we would need to wrap the SUMs you have above in a CALCULATE to perform context transition to get the right result. Also would need do YTD and PYTD calc for each date.