[Need Help] Adding measures to Matrix for Profit and Loss Report

[Brysonds]

Hi Everyone,

I am curious if it's possible to build a traditional P&L report in Power BI. I know that if I start with a table that is already in P&L layout, this works fine (see example 1). However, the data is not in a P&L layout already, it's just a flat file. I can easily add some dimnensionality to the flat file using conditional columns. For example, if the GL account is in this 400000 - 499999 range, then it's part of Sales, or if it's in the 500000 - 599999 then it's Cost of Goods sold. The challenge is showing the calculations (Gross Profit, Operating Profit, etc.) in the same Matrix view (see example 2). Has anyone been able to solve this? 

Example 1

If the data is stored in a P&L format, everything is fine. The problem is that our data is not stored like this...

Data Table

Report Output

Example 2

In real life, calculations like Gross Profit are not stored in the database. I create the calculations in Power BI using calculated measures

Gross Profit Measure

Gross Profit = CALCULATE(CALCULATE(SUM('Europe Finance Data'[Amount]), 'Europe Finance Data'[P&L Category]="Sales")+(CALCULATE(SUM('Europe Finance Data'[Amount]), 'Europe Finance Data'[P&L Category]="Cost of Goods Sold")))

I would like to include these calculations in the Matrix report as subtotals for the different P&L categories, but I don't see a way to do this in Power BI.

[VP]

You will not be able to add Measure to Row or column section.

In order to solve you problem, you can perform action in Power Query or Data model

For reference, There is excellent report prepared by partner (SENSDAT - Power BI Profit and Loss & Balance Sheet Solution):- SENSDAT - Power BI Profit and Loss & Balance Sheet Solution

[Brysonds]

Hi Everyone,

I am curious if it's possible to build a traditional P&L report in Power BI. I know that if I start with a table that is already in P&L layout, this works fine (see example 1). However, the data is not in a P&L layout already, it's just a flat file. I can easily add some dimnensionality to the flat file using conditional columns. For example, if the GL account is in this 400000 - 499999 range, then it's part of Sales, or if it's in the 500000 - 599999 then it's Cost of Goods sold. The challenge is showing the calculations (Gross Profit, Operating Profit, etc.) in the same Matrix view (see example 2). Has anyone been able to solve this? 

Example 1

If the data is stored in a P&L format, everything is fine. The problem is that our data is not stored like this...

Data Table

Report Output

Example 2

In real life, calculations like Gross Profit are not stored in the database. I create the calculations in Power BI using calculated measures

Gross Profit Measure

Gross Profit = CALCULATE(CALCULATE(SUM('Europe Finance Data'[Amount]), 'Europe Finance Data'[P&L Category]="Sales")+(CALCULATE(SUM('Europe Finance Data'[Amount]), 'Europe Finance Data'[P&L Category]="Cost of Goods Sold")))

I would like to include these calculations in the Matrix report as subtotals for the different P&L categories, but I don't see a way to do this in Power BI.

[ImkeF]

There's another approach to it as well: http://www.thebiccountant.com/2017/04/24/kpis-in-easy-profit-and-loss-for-powerbi/

It is more flexible and doesn't require to write all the different measures manually, but needs a bit more input with regards to model building / transforming your data.