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

[gediminasgruod]

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:

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?

[gediminasgruod]

@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]

@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])