Forum Discussion
Building a P&L matrix with calculated lines (data source is Dynamics 365 business central)
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.
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:
9 Replies
- DataInsights
Super User
Anonymous,
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.
- DataInsights
Anonymous,
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.
PBIdashboardsPost Patron
The dimension table grouping approach is correct for handling calculated P&L lines like EBITDA. For the specific case of EBITDA (Revenue GL codes minus Cost GL codes), the cleanest pattern is:
EBITDA =
VAR _rev = CALCULATE([Amount], DimGL[Category] = "Revenue")
VAR _cost = CALCULATE([Amount], DimGL[Category] = "Cost")
RETURN _rev - _costAdd this as a row in your grouping dimension table with a sort order that places it after Operating Income. The Matrix will display it as a calculated section header row.
One limitation this approach hits: after publishing, Finance users still can't add a variance column (Actual vs Budget, MoM) to the EBITDA row without the developer going back to Desktop. For D365 BC reports where Finance requests are frequent, Flexa Tables on AppSource adds variance columns directly in Power BI Service works with existing Dynamics 365 connections