Forum Discussion

marsclone's avatar
marsclone
Icon for Helper IV rankHelper IV
8 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
marsclone's avatar
marsclone
Icon for Helper IV rankHelper IV
8 years ago

Hi Lin,

 

Thank you for your support! The formulas looks like:

 

Total Revenue = Revenue1 + Revenue2

 

Total Cost = Cost1 + Cost2

 

EBITDA = Total Revenue - Total Cost

 

EBIT = EBITDA - Decrease1

 

EBT = EBIT -  Interest

 

Net Result = EBT - Taxes

 

Regards Marcel

 

Anonymous's avatar
Anonymous
Not applicable
8 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
    Icon for Helper IV rankHelper IV
    8 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
      8 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
        Icon for Helper IV rankHelper IV
        8 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.