Forum Discussion

Solle's avatar
Solle
Icon for Helper III rankHelper III
4 years ago
Solved

Subtracting two rows based on conditions

Hi,  As I have started to use PowerBI I have stumbled upon a problem, in which I haven't been able to find any posts or useful answers to.  I have the following columns which is used as rows in...
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Solle 

     

    It is simple to add date specific filtering to the original formula.

    New Holding Value = 
IF (
    SELECTEDVALUE ( 'Table'[PortfolioNbr] ) = 110,
    CALCULATE (
        SUM ( 'Table'[Holding Value] ),
        FILTER (
            ALL ( 'Table' ),
            [InstrumentSecurityID] = MAX ( 'Table'[InstrumentSecurityID] )&&[AsOfDate]=SELECTEDVALUE('Table'[AsOfDate])
        )
    ),
    IF (
        SELECTEDVALUE ( 'Table'[PortfolioNbr] ) = 120,
        CALCULATE (
            SUM ( 'Table'[Holding Value] ),
            FILTER (
                ALL ( 'Table' ),
                [InstrumentSecurityID] = MAX ( 'Table'[InstrumentSecurityID] )&&[AsOfDate]=SELECTEDVALUE('Table'[AsOfDate])
            )
        ),
        SELECTEDVALUE ( 'Table'[Holding Value] )
    )
)

    Did this result meet your expectations?

     

    Best Regards,

    Community Support Team _Charlotte

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

HotChilli's avatar
HotChilli
Icon for Community Champion rankCommunity Champion
4 years ago

Here's a column:

ColumnG = IF (TableR[PortfolioNbr] IN {110, 120}, CALCULATE(SUM(TableR[Holding Value]), ALLEXCEPT(TableR, TableR[InstrumentSecurityID])), TableR[Holding Value])

and that will work for the data as shown.

Maybe if the real data is more complex it might need some changes but see how your testing goes.

Solle's avatar
Solle
Icon for Helper III rankHelper III
4 years ago

Hi again, 
Thank you for your fast reply - The provided code seem to be applied to all the other portfolios as well. 

Beside the portfolios shown above, the same securityID might be present i.e. portfolio number 171, but what I need is the sum of portfolio 156 split upon securityID and then across portfolio number 110 and 120

Do you have any suggestions for this to work?

Thank you in advance.