Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Profit and Loss

Hi

I have a table consist of turnover, cost and other income/expense, and a second table as auxilary, like following

 

Table 1                                         Table 2

Sales1      $200                             Sales 1    Turnover

Sales2      $50                               Sales 2    Turnover

Cost1       $40                               Cost 1     COGS

Cost2       $30                               Cost 2     COGS

 

How can I put them into one matrix, showing total turnover and salse 1,2, and total COGS and cost1,2. like following

Turnover   Sales1  $200

                 Sales2  $50

Total Turnover      $250

COGS        Cost1   $40

                  Cost2  $30

Total COGS           $70                     Also, I need to calculate profit, profit % and other figure

EBIT

EBT

...

 

Please advise, attach a sample if possible. Thank you very much.
              

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi  Anonymous ,

    Table1:

    Table2:

    Here are the steps you can follow：

    1. Open the matrix, place [Sales_Cost] and [Type] of Table2 in Rows, and place [Amount] of Table1 in Values.

    Result：

    2. Enter Power query through Transform data, select Add Column – Column From Examples – From All Columns.

    3. Set the corresponding Index for [Type].

    4. Select column [Type], Column tools – Sort by column – column [Custom]

    Result:

    The matrix starts with "Turnover"

    5. In Format, find Row headers and set Stepped layout to off.

    6. In Format, find Subtotals, set Row subtotal position to Bottom, turn on Per row level, and set Type to OFF.

    Result：

     

    Calculate profit, profit %:

    Create measure:

    profit =
var _Turnover=CALCULATE(SUM('Table1'[Amount]),FILTER(ALL(Table2),'Table2'[Type]="Turnover"))
var _Cost=CALCULATE(SUM('Table1'[Amount]),FILTER(ALL(Table2),'Table2'[Type]="COGS"))
return
_Turnover - _Cost
    profit% =
var _Turnover=CALCULATE(SUM('Table1'[Amount]),FILTER(ALL(Table2),'Table2'[Type]="Turnover"))
var _Cost=CALCULATE(SUM('Table1'[Amount]),FILTER(ALL(Table2),'Table2'[Type]="COGS"))
return
DIVIDE( _Cost,_Turnover)

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

3 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    5 years ago

    Hi Anonymous ,

     

    You need to create a relationship on-to-many between both tables then you need to create measures in order to calculated the values for EBIT for example you would need something similar to:

     

    EBIT = CALCULATE(SUM('Table'[Value]), 'Table (2)'[Category] = "Turnover")-CALCULATE(SUM('Table'[Value]), 'Table (2)'[Category] = "COGS")

    Check result below and in attach PBIX.

     

    If you want to have a complete P&L you need to have a table with the full details of your P&L caqtegory and the associate to each one the measures has the previous one.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  Anonymous ,

    Table1:

    Table2:

    Here are the steps you can follow：

    1. Open the matrix, place [Sales_Cost] and [Type] of Table2 in Rows, and place [Amount] of Table1 in Values.

    Result：

    2. Enter Power query through Transform data, select Add Column – Column From Examples – From All Columns.

    3. Set the corresponding Index for [Type].

    4. Select column [Type], Column tools – Sort by column – column [Custom]

    Result:

    The matrix starts with "Turnover"

    5. In Format, find Row headers and set Stepped layout to off.

    6. In Format, find Subtotals, set Row subtotal position to Bottom, turn on Per row level, and set Type to OFF.

    Result：

     

    Calculate profit, profit %:

    Create measure:

    profit =
var _Turnover=CALCULATE(SUM('Table1'[Amount]),FILTER(ALL(Table2),'Table2'[Type]="Turnover"))
var _Cost=CALCULATE(SUM('Table1'[Amount]),FILTER(ALL(Table2),'Table2'[Type]="COGS"))
return
_Turnover - _Cost
    profit% =
var _Turnover=CALCULATE(SUM('Table1'[Amount]),FILTER(ALL(Table2),'Table2'[Type]="Turnover"))
var _Cost=CALCULATE(SUM('Table1'[Amount]),FILTER(ALL(Table2),'Table2'[Type]="COGS"))
return
DIVIDE( _Cost,_Turnover)

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • Aryna's avatar
    Aryna
    Icon for Post Partisan rankPost Partisan
    7 months ago

    Structuring a Profit and Loss report in Power BI like that can take a bit of trial and error, especially when you want subtotals like turnover, COGS and then calculated figures like profit or margins.

     

    If you're building a full report, it might also be worth looking at some ready-made Profit and Loss dashboard templates first. They already include the structure and can save quite a bit of time setting everything up: https://vidi-corp.com/profit-and-loss-dashboards/