Row wise calculation

[gnaneshwar]

I have requirement as per below:

P&L Statement has different values such as Revenue, Direct cost, Overheads, Depreciation& Interest,

Report shows P&L Statement wise Amount 

We want to show "Net Profit" calculated as "Revenue - (Direct cost+ Overheads+ Depreciation& Interest)" as mentioned below.

Any suggestions how to get this row-wise calculation

Thanks in advance.

Gnaneshwar

[v-xinruzhu-msft · Accepted solution]

Hi @gnaneshwar 

Based on your description, you can consider the following solution.

Sample data 

1.Create a statement table , it include a index column

Sample measure

Sum = SUM('Table'[Value])

Click the statement column and select sort by Index column.

2.Then create a measure

MEASURE =
VAR a =
    CALCULATE ( [Sum], 'Table'[Statement] = "Revenue" )
VAR b =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Month]
                IN VALUES ( 'Table'[Month] )
                    && [Statement] <> "Revenue"
        ),
        [Sum]
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Statement'[Statement] ) <> "NetProfit",
        CALCULATE ( [Sum], 'Table'[Statement] IN VALUES ( 'Statement'[Statement] ) ),
        a - b
    )

Then put the statement field of the statement table to row , and month field of table to column , then put the measure to the value in matrix.

Output

Best Regards!

Yolo Zhu

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