topic Profit and Loss Statement in Report Server

Profit and Loss Statement

GuestUser — Wed, 01 Jan 2020 10:02:36 GMT

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

Just to be more clear :

Profit Before Depreciation = Sales + COGS

Profit After Depreciation = Sales+ COGS - Depreciation

Pls let me know if the format is achievable ..

Any suggestions pls..

Re: Profit and Loss Statement

v-qiuyu-msft — Thu, 02 Jan 2020 01:55:47 GMT

Hi GuestUser,

Please create a calculated table with DAX below:

Table = UNION ( SUMMARIZE ( 'Master table - Dimension', 'Master table - Dimension'[Level6], "Actual", SUM ( 'Fact'[Amount] ) ), ROW ( "Level6", "Profit Before Depreciation", "Actual", CALCULATE ( SUM ( 'Fact'[Amount] ), FILTER ( 'Master table - Dimension', 'Master table - Dimension'[Level6] = "Sales" || 'Master table - Dimension'[Level6] = "COGS" ) ) ), ROW ( "Level6", "Profit After Depreciation", "Actual", CALCULATE ( SUM ( 'Fact'[Amount] ), FILTER ( 'Master table - Dimension', 'Master table - Dimension'[Level6] = "Sales" || 'Master table - Dimension'[Level6] = "COGS" ) ) - CALCULATE ( SUM ( 'Fact'[Amount] ), FILTER ( 'Master table - Dimension', 'Master table - Dimension'[Level6] = "Depreciation" ) ) ) )

Best Regards,
Qiuyun Yu

Re: Profit and Loss Statement

GuestUser — Tue, 07 Jan 2020 12:51:57 GMT

Hi,

i need the reports to interact with Date as well as other dimension , so can this solution still work?