Profit and Loss Statement

[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 

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..

[v-qiuyu-msft]

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 

[GuestUser]

Hi,

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