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

[Anonymous]

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.

[v-lionel-msft · Accepted solution]

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.