Forum Discussion

Solle's avatar
Solle
Helper 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 the report presented:

  • Aktivklasse (Top level info)
  • AltPortfolioName (Sub level info)
  • PortfolioNbr (Sub level info)
  • InstrumentSecurityID (Sub-sub level)

Beside that I have an [AsOfDate] which will be used in a slicer

The problem

My data looks like this

Aktivklasse AltPortfolioName PortfolioNbr InstrumentSecurityID Holding Value
Repo Repo 150 EU000x0000 100
Repo Repo 150 DK000x0000 500
Repo Repo 150 US000x0000 75
Bonds Bonds EU 110 EU000x0000 250
Bonds Bonds DK 120 DK000x0000 100


What I want to do is to SUM the [Holding Value] for each [InstrumentSecurityID] in [Aktivklasse] = Repo in either [PortfolioNbr] = 110 or [PortfolioNbr] = 120 depending on if the [InstrumentSecurityID] is present in the respective portfolio - Does anyone have a solution to this? The table I would like should be like this:

Aktivklasse AltPortfolioName PortfolioNbr InstrumentSecurityID Holding Value
Repo Repo 150 EU000x0000 100
Repo Repo 150 DK000x0000 500

Repo

 Repo 150 US000x0000 75
Bonds Bonds EU 110 EU000x0000 350
Bonds Bonds DK 120 DK000x0000 600


I have marked the part of the table which have changed to the sum. It is not necessarily needed for me to contain the repo data but it would be nice. 

Please let me know if anything else is required or needs to be explained.

Thank you in advance. 

  • 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.

17 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    Is the logic "if portfolionbr is 110 or 120 then sum the holding value for the same securityID, else holding value",

    i.e. is the portfolionbr hardcoded to those 2 values?

  • Solle's avatar
    Solle
    Helper III
    4 years ago

    Hi HotChilli, 

    Just to clarify what you are stating - The logic is that if SecurityID in PortfolioNbr 150 is present, then sum it in 110 or 120 respectively (this depends whether the SecurityID is in 110 or 120). 

    Yes PortfolioNbr and Holdingvalue is hardcoded and calculated from another system. 

    Thank you for your response 🙂
    Let me know if everything else is required from me.  

  • HotChilli's avatar
    HotChilli
    Community 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
      Helper 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.

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    How do we know that 110 and 120 are connected to portfolio 156 (did you mean 150?) and not connected to 171?

    • Solle's avatar
      Solle
      Helper III
      4 years ago

      Hi Chili, 
      Sorry for the confusion... The connection between 110,120 and 156 is something that is always the case but at the moment there is no data, which gives you that info, however I can create that if you wish? 

      It is simply because the way the data is structured is that 110 and 120 contains i.e. a number of bonds and then when they are lend out they are moved from 110 and 120 to 150 such that the holding value is now present in 150, but for the overall view I need to "remove" the portfolio in the report that I am trying to create. 

      Please let me know if you need anything from me, thank you for the response yet again, I appreciate it much!

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    "The connection between 110,120 and 156 is something that is always the case but at the moment there is no data, which gives you that info, however I can create that if you wish? "

    Somewhere in the data, we have to know that, so if you can create a small sample with that, then yes please. (i am still confused about 156 as that seemed to appear as if by magic)

    • Solle's avatar
      Solle
      Helper III
      4 years ago

      Hi Chili,
      Sorry again! I mean 150 when i typed 156.. 😕 

      Do I need to provide you with a sample then?

  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    Hi Solle 

     

    You can try the following methods.

    Maesure:

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

    Is this the result you want?

     

    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.

    • Solle's avatar
      Solle
      Helper III
      4 years ago

      Hi v-zhangti
      This is exactly what I want however it may seem as if it doesn't sum on the "AltPortfolioName" level and the "Aktivklasse" level when I have it in a Matrix, but when I unfold to a "InstrumentSecurityID" level it is working, beside that the AsOfDate filter does not seem to apply to this calculation? Do you know how to solve that?

      And thank you very much! This is super close to get me where I would like 😄 

      Best Regards,
      Solle 

      • v-zhangti's avatar
        v-zhangti
        Community Support
        4 years ago

        Hi, Solle 

         

        Summation is also possible when there is no "InstrumentSecurityID" level in the matrix.

        What do you want to use the AsOfDate filter for? What kind of result will be output? It is better to show it with a picture.

         

        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.