Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to create the calculated rows in P&L statement.

Hello Community Team,

 

We are trying to create the Profit and Loss statement in Power BI, I am doing this is first time and stuck at how to create the predefined total in P&L statement, Shall I use measure or calculated column.

 

Also would request you please help with dax, I have mark the filed below for your refernce.

 

Thnaks in advance.

 

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi Anonymous ,

    I created an original data table like this:
    (Notice that the value in the last row is empty.)

    In Power BI , first I calculated the value of the last row with a column:

     

     

    value of 'K+L-J' = 
VAR x=
CALCULATE(
    SUM(Sheet1[values]),
    FILTER(
        Sheet1,
        [Main title] = "Operating Profit-HG" || [Main title] = "Operating Profit-PAA" 
    )
)
VAR y =
CALCULATE(
    SUM( Sheet1[values]),
    FILTER(
        Sheet1,
        [Main title] = "Fixed Cost"
    )
)
RETURN
IF(
    [Main title] = "K+L-J",
    x-y,
    BLANK()
)

     

     

    Second, I merged [values] column and [value of ‘K+L-J’] column with a column:

     

     

    Column = 
IF(
    [values] = BLANK(),
    COMBINEVALUES("$", [value of 'K+L-J'], [values]),
    COMBINEVALUES("$", [values], [value of 'K+L-J'])
)

     

     

    Third, Transform data type:

     

    At last, add column in a matrix:

    Best regards,
    Lionel Chen

     

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

     

1 Reply

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

    I created an original data table like this:
    (Notice that the value in the last row is empty.)

    In Power BI , first I calculated the value of the last row with a column:

     

     

    value of 'K+L-J' = 
VAR x=
CALCULATE(
    SUM(Sheet1[values]),
    FILTER(
        Sheet1,
        [Main title] = "Operating Profit-HG" || [Main title] = "Operating Profit-PAA" 
    )
)
VAR y =
CALCULATE(
    SUM( Sheet1[values]),
    FILTER(
        Sheet1,
        [Main title] = "Fixed Cost"
    )
)
RETURN
IF(
    [Main title] = "K+L-J",
    x-y,
    BLANK()
)

     

     

    Second, I merged [values] column and [value of ‘K+L-J’] column with a column:

     

     

    Column = 
IF(
    [values] = BLANK(),
    COMBINEVALUES("$", [value of 'K+L-J'], [values]),
    COMBINEVALUES("$", [values], [value of 'K+L-J'])
)

     

     

    Third, Transform data type:

     

    At last, add column in a matrix:

    Best regards,
    Lionel Chen

     

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