Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
GuestUser
Helper V
Helper V

Profit and Loss Statement

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

 

 

2 REPLIES 2
v-qiuyu-msft
Community Support
Community Support

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"
                )
            )
    )
)

 

q1.PNG

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

 

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.