Profit and Loss Reports

[GuestUser]

Hi All,

I need to create PnL statement . I have below tables

Table1 : Master table - Dimension

Rowid    Level 5           Level 6

1              Profit             Sales

2              Profit              COGS

3             Depreciation    Depraciation

4            Income            Income

Table 2 - Fact:

AccountId   Amount

1                 10000

2                  200

3                  300

4                  400

My Report should look like below : (actual % values can be ignored)

Level 6                                     Actual   Actual%

Sales                                          1000      10%

COGS                                          200        20%

Profit Before Depreciation         1200        10%

Depreciation                                 300        20%

Profit After Depreciation             900          20%

Income                                        400          10%

I need the format like above ..basically Profit before and after Depreciation are not any values

And If I create measures , I am not able to put it in rows 

Any suggestions pls..

[mwegener · Accepted solution]

Hi @GuestUser ,

we call this a schema similar to Business Central.

We use the following way.

You may download my PBIX file from here.
Hope this helps.

The cool thing is that the values are calculated at runtime and so other filters, such as Calendar are taken into account.

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[GuestUser]

Hi @mwegener 

Thanks for the solution !!

I am currently implementing and will update accordingly

Just one question : the Sort Id column - can it be hidden? Since we are connected to SSAS through Connect Live option 

I can sort the data in the report after I add Sort Id Column in Report (and do Sort By Column in Matrix View)

But I dont want the Sort Id Visible-- Any ways to make it hidden ?

[mwegener]

Hi @GuestUser .

Yes, you can hide the SortID Column. (There is a column property)

But use it for the "Sort By Column" property of the "P&L" Column

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[GuestUser]

Hi,

One more help pls

Along with COGS , if I need COGS% , then can u help me out where exactly I need to add the DAX formula

Formula : COGS% = (COGS/Total Sales)*100

Report layout is somewhat like below

Level 6                                  Current year      Prev Year

Sales                                        1000               1233
COGS                                      200                 1333
COGS %                                   20%
Profit Before Depreciation       1200                 1444
Depreciation                              300                         1333
Profit After Depreciation           900                 111
Income                                      400                      1000

[mwegener]

Hi @GuestUser ,

it's not that easy, but it is possible.
You must add a row to the schema and an explicit calculation to the measure.

You may download my PBIX file from here.
Hope this helps.

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[mwegener]

Hi @GuestUser 

If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[GuestUser]

Hi @mwegener ,

I am trying to add previous year sales with below query

CALCULATE(sum('Fact'[Amount]),SAMEPERIODLASTYEAR('DATE'[Calender Date]))

But blank Value is being displayed. Any suggesions pls?

[mwegener]

Hi @GuestUser,

You may download my PBIX file from here.
Hope this helps.

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[az38]

Hi @GuestUser 

check sort by column button

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column#sort-using-the-sort-by-column-butto... Section Sort using the Sort by Column button

do not hesitate to give a kudo to useful posts and mark solutions as solution

[az38]

Hi @GuestUser 

use the next technique

1. be sure you have realtionships Table1 RowId - Table2 Account Id

2. Create a calculated table

P&L = 
var Sales = calculate(sum(Table2[Amount]);Table1[Level6]="Sales")
var COGS = calculate(sum(Table2[Amount]);Table1[Level6]="COGS")
var Depreciation = calculate(sum(Table2[Amount]);Table1[Level6]="Depreciation")
var Income = calculate(sum(Table2[Amount]);Table1[Level6]="Income")
return
{
(1;"Sales";Sales);
(2;"COGS";COGS);
(3;"Profit Before Depreciation";Sales+COGS);
(4;"Depreciation";Depreciation);
(5;"Profit After Depreciation";Sales+COGS-Depreciation);
(6;"Income";Income)
} 

3. to sort columns by your predefined order click left mouse on P&L[Value2] column in the Field pane go to the Modelling and sort by column - Value 1

check bix-file here https://ufile.io/yr6qrwew

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[GuestUser]

Just to be more clear 

Profit before Depreciation = Sales + COGS or (Sum of Level 5 where value = 'Profit')

Profit After Depreciation = Sales + COGS - Depreciation

Pls let me know if the format is achievable?