Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
)
@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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
26 | |
22 | |
13 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |