Formula - Sum values in a column based on another column

[gmasta1129]

Hello,

I created a matrix (portfolio code is the first column) and trying to figure out a formula to sum up the profit and loss column by Master portfolio code.  the columns are as follows...

For ex: Port Code 71111 and 71112  roll up into the same Master Port Code of 71111.  I need the profit and loss for 71111 and 71112 summed together (100,000+200,000) = 300,000

[v-yiruan-msft · Accepted solution]

Hi @gmasta1129 ,

You can create a measure as below to get the [Net Profit and Loss], and the value will be changed dynamically base on the slicer date selections.... Please find the details in the attachment.

Net Profit and Loss = 
CALCULATE (
    SUM ( 'Table'[Profit and Loss] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Master Portfolio Code]
            = SELECTEDVALUE ( 'Table'[Master Portfolio Code] )
    )
)

Best Regards

[gmasta1129 · Accepted solution]

Worked perfectly!! Thank you so much for your help! @v-yiruan-msft 

[gmasta1129]

Hello,

Thank you for the responses. I tried both formula's and they did not work.  I forgot to mentioned that i have a column labeled Run Date which is being used a a slicer.  Would i need to add this to the formula? I need the net profit and loss by date. 

See result below using @YukiK formula

[v-yiruan-msft · Accepted solution]

Hi @gmasta1129 ,

You can create a measure as below to get the [Net Profit and Loss], and the value will be changed dynamically base on the slicer date selections.... Please find the details in the attachment.

Net Profit and Loss = 
CALCULATE (
    SUM ( 'Table'[Profit and Loss] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Master Portfolio Code]
            = SELECTEDVALUE ( 'Table'[Master Portfolio Code] )
    )
)

Best Regards

[moizsherwani]

Here is the code

NetProfitAndLoss = 
VAR SelectedMasterPortfolioCode =
    SELECTEDVALUE ( Sampledata[Master PortfolioCode] )
RETURN
    CALCULATE (
        SUM ( Sampledata[Profit and Loss] ),
        FILTER (
            ALL ( Sampledata ),
            Sampledata[Master PortfolioCode] = SelectedMasterPortfolioCode
        )
    )

[YukiK]

I would create a measure like this:

Sum of Profit and Loss Per Master Code = 
    CALCULATE( 
        SUM (MyTable[profit and loss]),
        ALLEXCEPT( MyTable, MyTable[master code] )
    )

Hope this helps!