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...
Need Help
  • 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
            )
        )
)
wi11iamr's avatar
wi11iamr
Advocate II
7 years ago

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's avatar
marcorusso
Most Valuable Professional
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
            )
        )
)
  • wi11iamr's avatar
    wi11iamr
    Advocate II
    7 years ago
    Thanks Marco, this did just the trick!!