Forum Discussion

wi11iamr's avatar
wi11iamr
Advocate II
7 years ago
Solved

Nested SUMX performance impact within Tabular cube

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 inclu...
  • marcorusso's avatar
    marcorusso
    7 years ago

    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
                )
            )
    )