Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wi11iamr
Advocate II
Advocate II

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

 

 

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

View solution in original post

4 REPLIES 4
d_gosbell
Super User
Super User

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

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.

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

Thanks Marco, this did just the trick!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.