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:
Super UserAnonymous,
I would start by adding a grouping column to your Chart of Accounts table. The logic would be based on GL code (e.g., 4* = Revenue). Then, create a second table that contains all the groupings you want to display in the matrix. This second table allows you to group the Chart of Accounts groupings. For example, Revenue is included in Net Income as well as EBITDA. If you can attach an Excel mock-up of the desired end result, we can delve deeper.
Hi, thanks so much for coming back to me.
I have attached a brief example as well as the desired output.
The catagory and sub catagory desired output is working perfectly well, I am just unsure how to also include EBITDA. Maybe I need to add another category?
Best,
| Data from business central | ||||||||||||
| Calculated table | ||||||||||||
| Desired output | ||||||||||||
| Genral ledger entries | Chart of accounts | Calculated look up table | Desired Output | |||||||||
| GL Code | Amount | GL Code | Name | GL Code | Category | Sub category | Category | Sub Category | Amount | |||
| 1001 | £ 100 | 1001 | Revenue 1 | 1001 | Revenue | Product 1 | Revenue | Product 1 | £ 201 | |||
| 1002 | £ 101 | 1002 | Revenue 2 | 1002 | Revenue | Product 1 | Revenue | Product 2 | £ 205 | |||
| 1003 | £ 102 | 1003 | Revenue 3 | 1003 | Revenue | Product 2 | Revenue | Product 3 | £ 104 | |||
| 1004 | £ 103 | 1004 | Revenue 4 | 1004 | Revenue | Product 2 | Total Revenue | £ 510 | ||||
| 1005 | £ 104 | 1005 | Revenue 5 | 1005 | Revenue | Product 3 | ||||||
| 2001 | £ 50 | 2001 | Costs 1 | 2001 | Costs | Product 1 | Costs | Product 1 | £ 101 | |||
| 2002 | £ 51 | 2002 | Costs 2 | 2002 | Costs | Product 1 | Costs | Product 2 | £ 105 | |||
| 2003 | £ 52 | 2003 | Costs 3 | 2003 | Costs | Product 2 | Costs | Product 3 | £ 54 | |||
| 2004 | £ 53 | 2004 | Costs 4 | 2004 | Costs | Product 2 | Total Revenue | £ 260 | ||||
| 2005 | £ 54 | 2005 | Costs 5 | 2005 | Costs | Product 3 | ||||||
| EBITDA | £ 250 | |||||||||||
| EBITDA Margin | 49% | |||||||||||
Sorry, this is probably more helpul
- DataInsights
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?
