Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Optimize SUMX with SUMMARIZE

Hello!  I have a Pricing analysis report where I need to calculate the impact of pricing changes. The dax formulas work and produce the desired result however performance is really bad.  My Fact ta...
  • Anonymous's avatar
    Anonymous
    4 years ago

    Thank you for your input. I have optimised to the below. This took it from 11-15 seconds to 6-7 seconds. I will try to factor out  DIVIDE([_YTDLC],[_YTDQ],0) and DIVIDE([_PYDC],[_PYQ],0) in separate columns as they happen twice in the final calculation, but I was hoping my entire approach was wrong and I can get some major performance increase. When checking the Server Timings output, I have a lot of rows (50k) materialised multiple times (5 times). 

    PriceImpactLC:=
    VAR maxDate = MAX( Dim_Date[FiscalDate] )
    VAR maxYear = YEAR( maxDate )
    VAR _datesytd =
        DATESYTD(Dim_Date[FiscalDate])
    VAR _py =
        PARALLELPERIOD(Dim_Date[FiscalDate],-1,YEAR)
    
    RETURN SUMX(
            ADDCOLUMNS(
                ADDCOLUMNS(
                    CALCULATETABLE(
                    SUMMARIZE(
                        Fact_Pricing,
                        Fact_Pricing[Matched],
    		            Dim_Company[RegionTxt],
                        Fact_Pricing[CorporateMarket],
                        Fact_Pricing[CompanySurrId],
                        Fact_Pricing[MaterialSurrId],
                        Fact_Pricing[DocumentCurrency],
                        Fact_Pricing[LocalCurrency]
                    ), FILTER(ALL(Dim_Date), Dim_Date[FiscalDate]<=maxDate)),
    		    "_YTDQ", CALCULATE(SUM(Fact_Pricing[SalesQuantity]),_datesytd),
                "_PYQ",CALCULATE(SUM(Fact_Pricing[SalesQuantity]),_py),
                "_YTDLC", CALCULATE( SUM(Fact_Pricing[NetBillingRevenueLC]), _datesytd),
                "_YTDDC", CALCULATE( SUM(Fact_Pricing[NetBillingRevenueDC]), _datesytd),
                "_PYDC", CALCULATE( SUM(Fact_Pricing[NetBillingRevenueDC]), _py )
                ),
             "_PriceImpact", (DIVIDE([_YTDLC],[_YTDQ],0)-DIVIDE(DIVIDE([_YTDLC],[_YTDQ],0),1+DIVIDE(DIVIDE([_YTDDC],[_YTDQ],0)-DIVIDE([_PYDC],[_PYQ],0),DIVIDE([_PYDC],[_PYQ],0),0),0))*[_YTDQ]
            ),
    		[_PriceImpact]
            )