Forum Discussion

marsclone's avatar
marsclone
Helper IV
7 years ago

Calculated Rows P&L

I have been greatly helped by another user but i didn't find the right solution   This is my testfile   Test   I want to create a P&L in Power BI. I have my costs and revenues in one column whi...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

So this is possible both with measures or with columns, I will post measures however if you need to use it for slicers you will need columns. Also depending on the size of your table you may want to use columns as it may speed up your report.

 

You will need to change "Table" to whatever your table is called

 

Total Revenue = SUMX ( Table, Table[Revenue1] + Table[Revenue2] )

 

Total Cost = SUMX ( Table, Table[Cost1] + Table[Cost2] )

 

EBITDA = [Total Revenue] - [Total Cost]

 

EBIT = SUMX ( Table, [EBITDA] - Table[Decrease1] )

 

EBT = SUMX ( Table, [EBIT] - Table[Interest] )

 

Net Result = SUMX ( Table,  [EBT] - Table[Taxes] )

 

marsclone's avatar
marsclone
Helper IV
7 years ago

Thank you for your answer, but can you take a look at the test file?

Is your solution suitable for my situtation?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Ah ok I see that your data is in a narrow table. My measures were made assuming each item had its own column. For this format of data the measures would be as follows

     

    Revenue1 =
    CALCULATE ( SUM ( 'P&L'[Grootboek] ), 'P&L'[Ertragslage] = "Revenue1" )

     

    Revenue2 =
    CALCULATE ( SUM ( 'P&L'[Grootboek] ), 'P&L'[Ertragslage] = "Revenue2" )

     

    Total Revenue = [Revenue1] + [Revenue2]

     

    Costs1 =
    CALCULATE ( SUM ( 'P&L'[Grootboek] ), 'P&L'[Ertragslage] = "Costs1" )

     

    Costs2 =
    CALCULATE ( SUM ( 'P&L'[Grootboek] ), 'P&L'[Ertragslage] = "Costs2" )

     

    Total Costs = [Costs1] + [Costs2]

     

    EBITDA = [Total Revenue] - [Total Costs]

     

    Decrease1 =
    CALCULATE ( SUM ( 'P&L'[Grootboek] ), 'P&L'[Ertragslage] = "Decrease1" )

     

    EBIT = [EBITDA] - [Decrease1]

     

    Interest1 =
    CALCULATE ( SUM ( 'P&L'[Grootboek] ), 'P&L'[Ertragslage] = "Interest1" )

     

    EBT = [EBIT] -  [Interest1]

     

    Taxes =
    CALCULATE ( SUM ( 'P&L'[Grootboek] ), 'P&L'[Ertragslage] = "Taxes" )

     

    Net Result = [EBT] - [Taxes]

     

    Hope this helps

    • marsclone's avatar
      marsclone
      Helper IV
      7 years ago

      Yes! This is the way to calculate the desired outcomes.

       

      But if want to achieve the desired report, i (think) have to use (calculated) columns?

       

      How do we achieve that?

       

      Thank you for your support.