Subtracting two rows based on conditions

[Solle]

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]

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]

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]

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.

[Solle]

Hi @v-zhangti 

I have just added the image below. The AsOfDate filter is used to swap between months as it is a monthly report that I am creating. Please see the slicer that I am using to select dates. As you see below there is no sum value for "NewHoldingValue"

I have the following applied to my table

Please let me know if anything else is required?

Thank you for the fast reply! 

Best Regards,

[v-zhangti]

Hi, @Solle 

The data you provided earlier did not mention dates to correlate the date slicer with the results I provided. What kind of dates do you have in your original table?

Best Regards

[Solle]

Hi @v-zhangti 

Every month a new end of month date appears, so currently I have the following

However as the months go by there will be i.e. 31. March 2022 etc. Below is how it looks in the data. 

Sorry for not mentioning the date, I didnt think that, that had an impact on the results, as I am fairly new and is self-taught from the internet. 

Best Regards

[v-zhangti]

Hi, @Solle 

What does your date look like in relation to Holding Value? Or does the date have a relationship to other fields? The first time you provided data, was it for one of your months?

Best Regards

[Solle]

Hi @v-zhangti 

The date field is applied across all of the fields, meaning that you have date in a column where it is present - I am working with a very large data set, so everything is from the same source and everything have a date. 

The first time I presented data I just showed some test data, as I cannot post the real data. But what I am working with is Portfolios in which there are some InstrumentSecurityID's then on a monthly basis I receive an output file which contains new information as the market has developed during the month. I am creating this report to quickly do a performance report, so yes there is a relation to Holding Value and the relation is like:

AsOfDate	Aktivklasse	AltPortfolioName	PortfolioNbr	InstrumentSecurityId	Holding Value
31-01-2022	Repo	Repo	150	US000x0000	100
31-01-2022	Bonds	Bonds US	110	US000x0000	50
28-02-2022	Repo	Repo	150	US000x0000	200
28-02-2022	Bonds	Bonds US	110	US000x0000	300

An Example only involving Portfolio 110 with AsOfDate = 31-01-2022, but it is the same for 120 as it should be summed with 150 as well on a InstrumentSecurityID level. 
So what I want is to have a slicer selecting the data and then the following table which sums depending on the date selected by the slicer:

AsOfDate	Aktivklasse	AltPortfolioName	PortfolioNbr	InstrumentSecurityId	Holding Value
Selected by Slicer (in this example 31-01-2022)	Bonds	Bonds US	110	US000x0000	100 + 50 = 150

Please let me know if you need some furhter explanation and please be aware that the same InstrumentSecurityID can be present in other portfolios and the holding values from these shouldn't be included in the above. 

Best Regards

[v-zhangti · Accepted solution]

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]

"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]

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

Do I need to provide you with a sample then?

[HotChilli]

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

[Solle]

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]

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]

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.

[Solle]

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]

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?