Profit and Loss Statement

Question

Hi All,&nbsp;I need to create PnL statement . I have below tables&nbsp;Table1 : Master table - Dimension&nbsp;Rowid&nbsp; &nbsp; Level 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Level 61&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Profit&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sales2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Profit&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COGS3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Depreciation&nbsp; &nbsp; Depraciation4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Income&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Income&nbsp;Table 2 - Fact:AccountId&nbsp; &nbsp;Amount1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100002&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2003&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3004&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 400&nbsp;My Report should look like below :&nbsp;(actual % values can be ignored)&nbsp;Level 6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Actual&nbsp; &nbsp;Actual%Sales&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1000&nbsp; &nbsp; &nbsp; 10%COGS&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 200&nbsp; &nbsp; &nbsp; &nbsp; 20%Profit Before Depreciation&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1200&nbsp; &nbsp; &nbsp; &nbsp; 10%Depreciation&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;300&nbsp; &nbsp; &nbsp; &nbsp; 20%Profit After Depreciation&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;900&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20%Income&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 400&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10%&nbsp;I need the format like above ..basically Profit before and after Depreciation are not any valuesAnd If I create measures , I am not able to put it in rows&nbsp;&nbsp;Just to be more clear :&nbsp;Profit Before Depreciation = Sales + COGSProfit After Depreciation = Sales+ COGS - Depreciation&nbsp;Pls let me know if the format is achievable ..&nbsp;Any suggestions pls..&nbsp;&nbsp;

v-qiuyu-msft · Answer

Hi&nbsp;GuestUser,&nbsp;
&nbsp;
Please create a calculated table with DAX below:&nbsp;
&nbsp;
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"
                )
            )
    )
)

&nbsp;

&nbsp;
Best Regards,Qiuyun Yu&nbsp;

guestuser · Answer

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

Forum Discussion

Profit and Loss Statement

2 Replies

Related content

Calculate Gross margin in Profit and Loss statement

Bringing net profit from profit and loss statement into Balance Sheet

Profit and Loss Reports

Profit & Loss Table

How to create dynamic / special profit and loss