Forum Discussion

gnaneshwar's avatar
gnaneshwar
Regular Visitor
2 years ago
Solved

Row wise calculation

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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