Price - Volume Variance Analysis | DAX optimization | Resources Exceeded

[BenBell]

Hello, I'm seeking advice on any ways to optimize the following DAX measures to enable the engine to perform the calculation without any visual / report level filter or slicer.  On both desktop and publishing to the online service, I receive a resources exceeded error message when it tries to iterate through all 14M order line items YoY, in arriving at the card total without any filter.  I believe it's necessary for this calculation to be peformed as a DAX measure, since the stakeholder wants to slice by varying dimensions & granularities impacting whether or not a given product is recurring YoY within the given context and then arrive at its variance allocation to price vs volume, accordingly.  Below, I've put in bold font where I may not be following best practice.  Appreciate any help!

Sales Price (staging) =

        var PYTDSalesRebate =

                CALCULATE(SUM(Orders[Net Sales]),

                        Orders[Volume Rebate Flag] = "Rebate",

                        DimDate[YTD] = True,

                        DimDate[NbrYearsFromToday] = -1)

        var CYTDSalesRebate =

                CALCULATE(SUM(Orders[Net Sales]),

                        Orders[Volume Rebate Flag] = "Rebate",

                        DimDate[YTD] = True,

                        DimDate[NbrYearsFromToday] = 0)

        var PYTDSales =

                CALCULATE(SUM(Orders[Net Sales]),

                        Orders[Volume Rebate Flag] = "Other",

                        DimDate[YTD] = True,

                        DimDate[NbrYearsFromToday] = -1,

                        FILTER(VALUES(Orders[Item Number]), [YTD Period Classification] = "Recurring"))

        var CYTDSales =

                CALCULATE(SUM(Orders[Net Sales]),

                        Orders[Volume Rebate Flag] = "Other",

                        DimDate[YTD] = True,

                        DimDate[NbrYearsFromToday] = 0,

                        FILTER(VALUES(Orders[Item Number]), [YTD Period Classification] = "Recurring"))

        var PYTDUnits =

                CALCULATE(SUM(Orders[Quantity Shipped]),

                        Orders[Volume Rebate Flag] = "Other",

                        DimDate[YTD] = True,

                        DimDate[NbrYearsFromToday] = -1,

                        FILTER(VALUES(Orders[Item Number]), [YTD Period Classification] = "Recurring"))

        var CYTDUnits =

                CALCULATE(SUM(Orders[Quantity Shipped]),

                        Orders[Volume Rebate Flag] = "Other",

                        DimDate[YTD] = True,

                        DimDate[NbrYearsFromToday] = 0,

                        FILTER(VALUES(Orders[Item Number]), [YTD Period Classification] = "Recurring"))

        var PYTDASP =

                DIVIDE(PYTDSales, PYTDUnits, 0)

        var CYTDASP =

                DIVIDE(CYTDSales, CYTDUnits, 0)

        return

                ((CYTDASP - PYTDASP) * CYTDUnits) + (CYTDSalesRebate - PYTDSalesRebate)

 --the sales price measure branching seems necessary due to incorrect grand totals when displaying the sales price staging measure

Sales Price =

        IF(ISINSCOPE(Orders[Item Number]),

                [Sales Price],

                SUMX(SUMMARIZE(Orders, Orders[Item Number], "Sales Price", [Sales Price]), [Sales Price]))

YTD Period Classification =

        var CYTDSales =

                CALCULATE(SUM(Orders[Net Sales]),

                        DimDate[YTD] = True,

                        DimDate[NbrYearsFromToday] = 0)

        var PYTDSales =

                CALCULATE(SUM(Orders[Net Sales]),

                        DimDate[YTD] = True,

                        DimDate[NbrYearsFromToday] = -1)

        var CYTDUnits =

                CALCULATE(SUM(Orders[Quantity Shipped]),

                        DimDate[YTD] = True,

                        DimDate[NbrYearsFromToday] = 0)

        var PYTDUnits =

                CALCULATE(SUM(Orders[Quantity Shipped]),

                        DimDate[YTD] = True,

                        DimDate[NbrYearsFromToday] = -1)

        var CYTDCost =

                CALCULATE(SUM(Orders[Extended Cost Quantity Shipped]),

                        DimDate[YTD] = True,

                        DimDate[NbrYearsFromToday] = 0)

        var PYTDCost =

                CALCULATE(SUM(Orders[Extended Cost Quantity Shipped]),

                        DimDate[YTD] = True,

                        DimDate[NbrYearsFromToday] = -1)

        return

                SWITCH(TRUE(),

                    CYTDSales <> 0 && PYTDSales <> 0 && CYTDCost <> 0 && PYTDCost <> 0 && CYTDUnits <> 0 && PYTDUnits <> 0,

                    // TRUE(), FALSE())

                        "Recurring",

                    PYTDSales <> 0 && PYTDCost <> 0 && PYTDUnits <> 0,

                        "Discontinued",

                    CYTDSales <> 0 && CYTDCost <> 0 && CYTDUnits <> 0,

                        "New",

                        "Other")

[BenBell · Accepted solution]

I may have at least improved the measure with the following update below.  The report card & table visuals run quicker without the error message on desktop with this revision, at least.  Perhaps, still not ideal, not sure.  

Sales Price (Staging) =

        var PYTDSalesRebate =

                CALCULATE(SUM(Orders[Net Sales]),

                        Orders[Volume Rebate Flag] = "Rebate",

                        DimDate[YTD] = True,

                        DimDate[NbrYearsFromToday] = -1)

        var CYTDSalesRebate =

                CALCULATE(SUM(Orders[Net Sales]),

                        Orders[Volume Rebate Flag] = "Rebate",

                        DimDate[YTD] = True,

                        DimDate[NbrYearsFromToday] = 0)

        var PYTDSales =

                IF([YTD Period Classification] = "Recurring",

                    CALCULATE(SUM(Orders[Net Sales]),

                            Orders[Volume Rebate Flag] = "Other",

                            DimDate[YTD] = True,

                            DimDate[NbrYearsFromToday] = -1),

                    0)

        var CYTDSales =

                IF([YTD Period Classification] = "Recurring",

                    CALCULATE(SUM(Orders[Net Sales]),

                            Orders[Volume Rebate Flag] = "Other",

                            DimDate[YTD] = True,

                            DimDate[NbrYearsFromToday] = 0),

                    0)

        var PYTDUnits =

                IF([YTD Period Classification] = "Recurring",

                    CALCULATE(SUM(Orders[Quantity Shipped]),

                            Orders[Volume Rebate Flag] = "Other",

                            DimDate[YTD] = True,

                            DimDate[NbrYearsFromToday] = -1),

                    0)

        var CYTDUnits =

                IF([YTD Period Classification] = "Recurring",

                    CALCULATE(SUM(Orders[Quantity Shipped]),

                            Orders[Volume Rebate Flag] = "Other",

                            DimDate[YTD] = True,

                            DimDate[NbrYearsFromToday] = 0),

                    0)

        var PYTDASP =

                DIVIDE(PYTDSales, PYTDUnits, 0)

        var CYTDASP =

                DIVIDE(CYTDSales, CYTDUnits, 0)

        return

                ((CYTDASP - PYTDASP) * CYTDUnits) + (CYTDSalesRebate - PYTDSalesRebate)

[v-yanjiang-msft]

Hi @BenBell ，

Glad you figure it out by yourself. Would you mind accept your reply as solutions? Then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!

Best Regards,
Community Support Team _ kalyj

[v-yanjiang-msft]

Hi @BenBell ,

According to the formula, you havn't used too complicated function, but there're too many variables in the formula, it spends too much CPU in the calculation. I think this led to the error.

To prevent too heavy calculation in a measure, I suggest you try to break it down into several measures or calculated columns.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BenBell]

Appreciate your response!  I'm not sure I agree though that a measure branching approach would be more performant than the variables approach I've taken.
https://www.sqlbi.com/articles/variables-in-dax/

[BenBell]