Forum Discussion
wi11iamr
Advocate II
7 years agoNested 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
Most Valuable Professional
7 years agoAssuming 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
Advocate II
7 years agoThanks Marco, this did just the trick!!