Nested SUMX performance impact within Tabular cube

[wi11iamr]

To ensure the correct calculation of Forecast Accuracy at the Sub Total and Grand Total levels, I'm using the SUMX with the VALUES funciton to iterate record individually.

My approach is to include a SUMX iterator for each dimension by which the values will be analysed. This results in me having 5 nested SUMX's.

When calculating percentages it is even more compounded to 10 SUMX's. 

My Tabular SSAS cube then has 12 different measures using the same concept, and from what I can tell these measures are killing my Analysis Services.

Below is one of my code snippets. Any recommendations on how to improve the performance while still maintaining the integrity of my results at a Sub Total and Grand Total level?

Forecast Accuracy :=
IF (
    [Forecast Amt] >= [Shipped Amt],
    0,
    1
        - (
            DIVIDE (
                SUMX (
                    VALUES ( 'Customers'[AccNo] ),
                    SUMX (
                        VALUES ( 'Calendar'[Year] ),
                        SUMX (
                            VALUES ( 'Calendar'[Month] ),
                            SUMX (
                                VALUES ( Items[Supplier] ),
                                SUMX ( VALUES ( Items[ItemCode] ), [Forecast Amt] )
                            )
                        )
                    )
                ),
                SUMX (
                    VALUES ( 'Customers'[AccNo] ),
                    SUMX (
                        VALUES ( 'Calendar'[Year] ),
                        SUMX (
                            VALUES ( 'Calendar'[Month] ),
                            SUMX (
                                VALUES ( Items[Supplier] ),
                                SUMX ( VALUES ( Items[ItemCode] ), [Shipped Amt] )
                            )
                        )
                    )
                ),
                0
            )
        )
)

[d_gosbell]

It's hard to say without knowing what issue you were trying to work around by putting in those sumx statements

What problem were you trying to solve by adding these?

What's the difference in output between your code and the following:

Forecast Accuracy :=
IF (
    [Forecast Amt] >= [Shipped Amt],
    0,
    1 - DIVIDE( [Forecast Amt], [Shipped Amt] )
)

[wi11iamr]

The SUMX's are simply to ensure that my Grand Totals calculate correctly.

My understanding is that using SUMX with VALUES forces the calculation to iterate at a row level before calculating the Grand Total.

If I don't use the SUMX with VALUES, then my Grand Totals are incorrect.

Perhaps I'm not explaining myself very clearly, but it's a scenario/trick I learned in @marcorusso's DAX course last year.

[marcorusso · Accepted solution]

Assuming you have a business logic in your measures that require the SUMX approach (I cannot be sure of that), you could improve the performance by avoding nested loops. Replace ForecastTable and ShippingTable with the name of the tables containing data using by the corresponding measures.

Forecast Accuracy :=
IF (
    [Forecast Amt] >= [Shipped Amt],
    0,
    1
        - (
            DIVIDE (
                SUMX (
                    SUMMARIZE (
                        ForecastTable,
                        'Customers'[AccNo],
                        'Calendar'[Year],
                        'Calendar'[Month],
                        Items[Supplier],
                        Items[ItemCode]
                    ),
                    [Forecast Amt]
                ),
                SUMX (
                    SUMMARIZE (
                        ShippingTable,
                        'Customers'[AccNo],
                        'Calendar'[Year],
                        'Calendar'[Month],
                        Items[Supplier],
                        Items[ItemCode]
                    ),
                    [Shipped Amt]
                ),
                0
            )
        )
)

[wi11iamr]

Thanks Marco, this did just the trick!!