DAX Performance Tuning - Income Statement

[JRogersLebara]

I have created an Income Statement using 'Financial Reporting Matrix' by ProfitBase (screenshot below with values removed for sensitivity).

The performance of the visual is very slow despite only having 100k rows in the General Journal data - changing a filter takes 11-20 seconds and even longer when published to Power BI Service.

The basic data model is as follows (for demonstration purposes I've removed other lookup tables such as Calendar, Entity etc):

- 'P&L Rank' is a manually written template for the income statement:

- 'Main Accounts' is a mapping table that assigns each GL account a category (same categories as the 'Metric' column in the 'P&L Rank' table)

- 'General Journal' is the fact table

The 'row' section of the matrix contains the following Measure:

P&L ACTUALS = 
SWITCH(
    MAX('📋P&L RANK'[MEASURE]),
    0, [ACTUAL] / SELECTEDVALUE('🔎UNITS'[DIVISION]),
    1, '_GLOBAL MEASURES'[GROSS REVENUE ACTUALS] / SELECTEDVALUE('🔎UNITS'[DIVISION]),
    2, [MOBILE PROFIT ACTUALS] / SELECTEDVALUE('🔎UNITS'[DIVISION]),
    3, [MOBILE PROFIT % ACTUALS],
    4, [GROSS PROFIT ACTUALS] / SELECTEDVALUE('🔎UNITS'[DIVISION]),
    5, [GROSS MARGIN ACTUALS],
    6, [TOTAL OPEX ACTUALS] / SELECTEDVALUE('🔎UNITS'[DIVISION]),
    7, [EBITDA PRE-EXCEPTIONALS ACTUALS] / SELECTEDVALUE('🔎UNITS'[DIVISION]),
    8, [REPORTED EBITDA ACTUALS] / SELECTEDVALUE('🔎UNITS'[DIVISION]),
    9, [WORKING CAPITAL MOVEMENTS ACTUALS] / SELECTEDVALUE('🔎UNITS'[DIVISION]),
    10, [OPERATING CASHFLOW ACTUALS] / SELECTEDVALUE('🔎UNITS'[DIVISION]),
    11, [CASH CONVERSION RATIO ACTUALS]
)

An example of the measures referenced in the above 'P&L Actuals' measure is as follows:

GROSS PROFIT ACTUALS = 
SUMX(
    FILTER(
        ALL('📋P&L RANK')
        ,'📋P&L RANK'[INDEX] IN {1, 2, 3, 4, 8, 9, 10}
        )
    ,'📒GENERAL JOURNAL'[ACTUAL]
)

Performance Analyser:

My suspicion is that the FILTER argument is what's causing the performance issues but I haven't been able to work out an alternative solution - any ideas Power BI community?

[amitchandak]

@JRogersLebara , Measure looping can slow down measure.

Can you move the measure calculation(from base column) in the measure P&L as var and then try

[JRogersLebara]

Hi @amitchandak , are you able to clarify what you meant in your reply, please?

Thanks

[JRogersLebara]

Hello @amitchandak, I am not sure what you mean, could you please clarify?

Thanks