DAX Performance Tuning - Income Statement (Third Attempt)

[JRogersLebara]

NOTE: I have posted this question twice before (2962974 and 2976244) but have not received a solution so far

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?

P.S. I have taken some time to create a pared-down version of my file with randomised data and all other sensitive data removed here:

https://www.dropbox.com/s/v8oc7bk7umj1jmq/Power%20BI%20Example.pbix?dl=0

[tex628 · Accepted solution]

Hi @JRogersLebara,

First of all, the standard matrix visualization is almost exactly 3 times faster than the custom visual:

I'm not familiar with this visual by profitbase, but if you can manage to produce the required report from the matrix instead you're cutting 2/3's of the loadtime. 

Furthermore, i took a look into the calculations and it doesn't appear that any individual calculation is weighing things down. I would propose changing the background-structure to avoid the SWITCH/FILTER combination, as i would not expect this level of performance for 100K rows. 

I'm usually running something along the lines of this:

Which is then unpivoted in the transform so that both summarizations (gray) and rows can be mapped to a GL Account dimension table. 

Hope this helps! 

/ J

[JRogersLebara]

Hi @tex628,

The Profitbase matrix offers formatting benefits and also is very fast to use once slicers have been set (the initial increased load time when opening the tab or changing a slicer is offset by instantaneous drill-down. The normal matrix visual has to load every time the user drills down a level in the matrix, which we decided was more frustrating than the extended load after a slicer change).

In terms of your second suggestion, I think I partially follow but not completely - I have created a second file sort of following your advice but am not clear on how to replicate an identical matrix to the first file I shared using your solution - maybe you wouldn't mind taking a look and pointing out where I've misunderstood you?:

 tex628 solution 

It may also be worth noting that our GL accounts don't follow a perfectly linear mapping (i.e. revenue may be 100-150, then CoS 151-153, then revenue again 154-160 etc)

Thanks,

Jake