Forum Discussion
Alternative for AverageX
Hi, I have following working dax code. Since the data volume is high, it runs slow and gives memory error when filtered to lowest level. Is there a better alternative to using @AverageX?
The code is:
Average Balance (Period) = AVERAGEX( filter ( ALLSELECTED('Date'[Date])|and('Date'[Date]>=min('Date'[Date])|'Date'[Date] <= max ('Date'[Date])))| [Daily Average Balance] )
where,
Daily Average Balance = if (ISBLANK([(Cumulative) Opening Balance])| [(Cumulative) Closing Balance]| ([(Cumulative) Closing Balance]+[(Cumulative) Opening Balance])/2 )
Since [Daily Average Balance], [(Cumulative) Closing Balance] & [(Cumulative) Opening Balance] all are measures, I am unable to use Average function.
Thanks in advance!
Shailee.
7 Replies
Hi Anonymous
could you try to rewrite the code to this:
Average Balance (Period) = AVERAGEX ( values(account number), [Daily Average Balance] )The idea is that the daily average balance has to be computed for each account number, and after that compute the average balance for all accounts. There is no need to include any time handling in the expression, it will be evaluated in the context from the report. E.g. if the report has a month slicer and April is selected, then the measure will calculate the average balance of April
Cheers,
SturlaThough your suggestion improves the performance, but it does not yield the desired results. It does not consider the dates when there was no transaction for a particular Accound Code. Also I have multiple hierarchy levels for Account code, and report can be drawn at any level.
hm,
not sure if this will yield any better performance:
Average Balance (Period) = VAR _minDate = MIN ( 'Date'[Date] ) VAR _maxDate = MAX ( 'Date'[Date] ) RETURN AVERAGEX ( FILTER ( ALL ( 'Date' ), 'Date'[Date] >= _minDate && 'Date'[Date] <= _maxDate ), [Daily Average Balance] )If you provide some sample data it will be easier to help you
