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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

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 7
sturlaws
Resident Rockstar
Resident Rockstar

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,
Sturla

Anonymous
Not applicable

@sturlaws 

 

Though 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

Anonymous
Not applicable

@sturlaws 

There was not a major performance improvement with your last suggestion.

Following is relevant sample dataset-

This is the Accounting table on which Average Balance (rolling) needs to be calculated on [Daily Average Balance], which is a measure based on Home_Currency_Amount, Debit_Credit, and related Date and GL Master tables. This measure works perfectly fine separately.

source_system_code

entity_code

effective_date

posted_date

SubLob_Code

GL_code

home_currency_amount

debit_credit

ORA01

181

04/01/2020

04/01/2020

183

727104

213

DR

ORA01

101

04/01/2020

04/01/2020

G41

733102

1269.82

DR

ORA01

101

04/01/2020

04/01/2020

G41

733102

1006.5

CR

ORA01

181

04/01/2020

04/01/2020

E92

734113

303

CR

ORA01

181

05/01/2020

05/01/2020

183

727102

66

DR

 

The Accounting table is linked to a Date Table through Effective Date and following GL Master table (linked through SourceSystemCode + GL Code):

Source_System_Code

GL_CODE_DESCRIPTION

GL Code Level 1

GL Code Level 2

GL Level 3

ACCOUNT_TYPE

IMB01

KRISHNA PRATAP SINGH

KRISHNA PRATAP SINGH

Trade payables to non-related parties

Financial Liability

Liability

ORA01

Prepayment Migration A/c

Prepayment Migration A/c

Control Accounts

Non Financial Assets

Assets

ORA01

Salaries and wages - Sharing

Salaries and wages - Sharing

Salaries and wages - Sharing

Employee Benefit Expenses

Expenses

 

Thanks a lot for your help!

what do the code for  [(Cumulative) Closing Balance] & [(Cumulative) Opening Balance] look like?

Anonymous
Not applicable

@sturlaws 

 

(Cumulative) Closing Balance =
CALCULATE([Closing Balance]|
filter ( ALL('Date'[Date])|'Date'[Date] <= max ('Date'[Date]))
)

(Cumulative) Opening Balance =
CALCULATE([Closing Balance]|
filter ( ALL('Date'[Date])|'Date'[Date] <= (max ('Date'[Date])-1))
)

 

where Closing Balance =

Var Amount = Sum(Accounting[Home_Currency_Amount]))

Var Debit_Amt = CALCULATE([Amount],Accounting[Debit_Credit]="DR")

Var Credit_Amt = CALCULATE([Amount],Accounting[Debit_Credit]="CR")

Return

Debit_Amt - Credit_Amt

hmm,

 

so the way the opening and closing balance is calculated, is to sum up all prior transactions. So if a report user is trying to see the average daily balance for a month, using averagex, this process is repeated for each day. And if the report is drilled down to GL Code Level 1, the number of calculations is multiplied with number of GL Code Level 1 visible in the visual. With a table with small number of rows it will work. With a table with a high number of rows, it will be slow and you might run out of memory.

I think you will have to create a table containing the opening and closing balance for each account for each day in order to imrpove the query speed. 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors