Forum Discussion
Building a P&L matrix with calculated lines (data source is Dynamics 365 business central)
Anonymous,
Try this solution using the native matrix. It requires more setup, however, and the result is a matrix that may not meet the formatting requirements. I recommend trying the Profitbase Financial Reporting Matrix. Finance users love it, as it offers many advantages including the ability to create subtotals, add blank rows, apply specific formatting, etc.
Add EBITDA to the Lookup table, along with an Index column for sorting Category:
Data model:
In the GL Entries table, create a calculated column to flip the sign. You can also do this in Power Query.
Adjusted Amount = SWITCH ( TRUE, 'GL Entries'[GL Code] >= 1001 && 'GL Entries'[GL Code] < 2000, 'GL Entries'[Amount], 'GL Entries'[GL Code] >= 2001 && 'GL Entries'[GL Code] < 3000, 'GL Entries'[Amount] * -1 )Create measure:
Total Amount = CALCULATE ( SUM ( 'GL Entries'[Adjusted Amount] ), CROSSFILTER ( 'Chart of Accounts'[GL Code], 'Lookup Table'[GL Code], BOTH ) )Result:
Hi and thanks for your solution, I have successfully implement it in my model, the difference was that in my case GL Code was not number it is stored as a text like: 1000-12. Anyways, solution works and it's great, but can you please explain to me how the EBITDA calculations works? I mean, understand that Revenue and Opex is sum of amount, but how and where EBITDA calculates?
Super User
Glad you were able to adapt the solution. In the example, EBITDA is simply a grouping of all accounts. In the Lookup table, note that all GL Codes in Revenue and Costs are also in EBITDA.