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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
JRogersLebara
Frequent Visitor

DAX Performance Tuning - Income Statement

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.

JRogersLebara_0-1670859526286.png

 

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

JRogersLebara_1-1670859674630.png

 

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

 

JRogersLebara_2-1670859741068.png

- '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:

JRogersLebara_3-1670860091917.png

 

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?

 

3 REPLIES 3
amitchandak
Super User
Super User

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

 

Thanks

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

Thanks 

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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