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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gediminasgruod
Frequent Visitor

Power BI slow DAX performance in P&L matrix visual / optimization posibilities

Hello,

 

We have recently created a P&L visual by using matrix table and SWITCH( TRUE() logic (I used similar logic from this Enterprise DNA video: https://www.youtube.com/watch?v=mUVz59xCRMI&list=LL&index=9). 

 

The P&L table does a good job at showing detailed financial results of our business, however, the performance of this visual is extremely slow (it takes 17921 ms just to drill down one line in the P&L). We have already tried various methods of optimization i.e. dropping out columns which we don't need in our report, selecting only relevant dates, aggregating data where we can, etc. 

 

Currently we have used these steps to create our P&L:


  • We created a P&L mapping table (PNL Grouping'[P&L items (normalized)])
    and conected it with other necessary data for our calculations as we would do with any other kind of mapping;
  • We created separate measures for every row in our P&L:
    • ACT P&L Revenue = CALCULATE (DIVIDE(SUM('R_Main PIVOT'[Amount.1]),1000) , 'PNL Grouping'[P&L items (normalized)] in { "Revenue item level2", "Revenue item level2", "Revenue item level2", "Revenue item level2", "Revenue item level2", "Revenue item level2"} );
    • ACT P&L Other revenue = CALCULATE ( DIVIDE(SUM('R_Main PIVOT'[Amount.1]),1000) , 'PNL Grouping'[P&L items (normalized)] = "Revenue item level2" );
    • ACT P&L Total revenue = [ACT P&L Revenue] + [ACT P&L Other revenue]
    • ACT P&L Variable costs = CALCULATE ( divide(SUM('R_Main PIVOT'[Amount.1]),1000), 'PNL Grouping'[P&L items (normalized)] IN { "Cost item level2", "Cost item level2", "Cost item level2", "Cost item level2", "Cost item level2", "Cost item level2", "Cost item level2", "Cost item level2", "Cost item level2", "Cost item level2" } ) * -1
    • We have continued the same P&L logic with other items as well (EBIT, EBITDA, etc.).
  • We finaly create a final DAX measure where we added all these P&L items together:
  • Actual, kEUR =
    VAR CurrentItem =
    SELECTEDVALUE ( 'PNL Grouping'[P&L items (normalized)] )
    RETURN
    SWITCH (
    TRUE (),
    CurrentItem = "Revenue", [ACT P&L Revenue],
    CurrentItem = "Other revenue", [ACT P&L Other revenue],
    CurrentItem = "Total revenue", [ACT P&L Total revenue],
    CurrentItem = "Variable costs", [ACT P&L Variable costs],
    CurrentItem = "Fixed costs", [ACT P&L Fixed costs],
    CurrentItem = "EBITDA", [ACT P&L EBITDA],
    CurrentItem = "Total depreciation", [ACT P&L Total depreciation],
    CurrentItem = "EBIT", [ACT P&L EBIT],
    CurrentItem = "Finance expenses", [ACT P&L Total financial expenses],
    CurrentItem = "Net income before tax, extraordinary", [ACT P&L Net income before tax, extraordinary],
    CurrentItem = "Net income before tax", [ACT P&L Net income before tax],
    CurrentItem = "Tax", [ACT P&L Tax],
    CurrentItem = "Net result", [ACT P&L Net result]
    )

The final table looks like this:

gediminasgruod_0-1636025439911.png

 

I was wondering, maybe anyone of you who have created P&L visuals have encountered similar performance issues? Or should we use anoher approach when making this P&L?

2 REPLIES 2
gediminasgruod
Frequent Visitor

@V-pazhen-msft thank you for the your message. I have tried to optimize the formula but the performance is still pretty slow.

 

Maybe there's a DAX formula that would allow me to show result of subtotals like EBITDA but wouldn't allow users to filter it down?

V-pazhen-msft
Community Support
Community Support

@gediminasgruod 

 

The only way I can think of is to skip checking true/false for each item, this should helps logically. Take a try see if shortens the speed.

 

  • Actual, kEUR =
    VAR CurrentItem =
    SELECTEDVALUE ( 'PNL Grouping'[P&L items (normalized)] )
    RETURN
    SWITCH (
    CurrentItem,
    "Revenue", [ACT P&L Revenue],
    "Other revenue", [ACT P&L Other revenue],
    "Total revenue", [ACT P&L Total revenue],
    "Variable costs", [ACT P&L Variable costs],
    "Fixed costs", [ACT P&L Fixed costs],
    "EBITDA", [ACT P&L EBITDA],
    "Total depreciation", [ACT P&L Total depreciation],
    "EBIT", [ACT P&L EBIT],
    "Finance expenses", [ACT P&L Total financial expenses],
    "Net income before tax, extraordinary", [ACT P&L Net income before tax, extraordinary],
    "Net income before tax", [ACT P&L Net income before tax],
    "Tax", [ACT P&L Tax],
    "Net result", [ACT P&L Net result])

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.