Forum Discussion
wi11iamr
7 years agoAdvocate 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 inclu...
- 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 ) ) )
marcorusso
7 years agoMost Valuable Professional
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
7 years agoAdvocate II
Thanks Marco, this did just the trick!!