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 DataInsights, thanks for coming back to me.
I think option 2 is the best option. I have started this and works well, however I am unsure how I can group based for EBITDA when it is a product of all revenue GL codes - all cost GL codes?
Best,
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: