Building a P&L matrix with calculated lines (data source is Dynamics 365 business central)

[dannywallis1994]

Hi all, 

Wondering if someone can help. 

I have linked my PBI file to Dynamics 365 business central and pulled through both the genral ledger and chart of accounts tables and created a relationship between the two which is working well. I also have a calandar table which has a relationship with the genral ledger. 

However, I would like to calculate some additional P&L lines which are not in my genral ledger e.g EBITDA. 

What is the best way to do this? Do i need to created a calculated table? I belive it is best that I only have 1 value field as I am looking to add a switch in to compare to prior year, quarter etc. 

Thanks in advance for your help. 

[DataInsights]

@dannywallis1994,

Here are two options:

1. Use the Profitbase Financial Reporting Matrix. It provides additional functionality such as being able to create subtotal lines. It's a free, certified visual, but certain features require a license to remove the watermark.

https://appsource.microsoft.com/en-us/product/power-bi-visuals/wa200000642?tab=overview 

2. Create a dimension table that contains the groupings for lines such as EBITDA. Use this dimension table as matrix rows to get the desired groupings.

[dannywallis1994]

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, 

[DataInsights]

@dannywallis1994,

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.

[dannywallis1994]

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%

[dannywallis1994]

Sorry, this is probably more helpul 

[DataInsights · Accepted solution]

@dannywallis1994,

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: