SUM a column with one filter removed

[khizerk]

Hi. I have been trying to solve my problem, which should in theory be simple, but not quite sure why all that I have tried cannot resolve it.

I have the following tables:

Accounts:

AccountID		Account Name
1000		Account A
1001		Account B
1002		Account C
1003		Account D

Transactions - Group 1

Account		SecID		Date		Amount
1002		101		01-Jan-22		100
1002		102		16-Jan-22		200
1003		103		31-Jan-22		-150

Transactions - Group 2

Account		SecID		Date		Amount
1000		ABC		01-Jan-22		400
1000		ABC		01-Jan-22		100
1000		PQR		16-Jan-22		200
1001		XYZ		31-Jan-22		50

Securities

SecID		SecName
ABC		ABCName
PQR		PQRName
XYZ		XYZName
101		101Name
102		102Name
103		103Name

I am looking to create a pivot table as follows. Total would be a measure which sums up all amounts in the account (for all securities) if security name = ABC. All other filters, including account name, dates etc. are to be retained.

Account Name		Security Name		Amount		Total
Account A		ABC		500		700
Account A		PQR		200
Account B		XYZ		50
Account C		101		100
Account C		102		200
Account D		103		-150

I have tried various solutions using all and allselected, but either it ignores filter contexts which should remain or it doesn't remove the security filter when calculating total for each row.

Thanks!

[tamerj1 · Accepted solution]

Hi @khizerk 
Here is a sample fil with the solution https://we.tl/t-UCxwU8Fjs9

Total = 
VAR T1 = SUMMARIZE ( Transactions1, Accounts[Account Name], Securities[SecID] )
VAr T2 = SUMMARIZE ( Transactions2, Accounts[Account Name], Securities[SecID] )
VAR T3 = UNION ( T1, T2 )
VAR T4 = FILTER ( T3, [SecID] = "ABC" )
VAR T5 = DISTINCT ( SELECTCOLUMNS ( T4, "@AcountName", [Account Name] ) )
RETURN
    CALCULATE (
        [Amount],
        Accounts[Account Name] IN T5,
        REMOVEFILTERS ( Securities )
    )

[khizerk]

Thanks! This worked well!

I think I understand how the code works, but for clarity can you please explain this piece?

VAR T5 = DISTINCT ( SELECTCOLUMNS ( T4, "@AcountName", [Account Name] ) )
RETURN
    CALCULATE (
        [Amount],
        Accounts[Account Name] IN T5,
        REMOVEFILTERS ( Securities )

[amitchandak]

@khizerk , If you used a joined table filter, it will continue to filter display value. Use an independent table

Need of an Independent Table in Power BI - Exclude: https://youtu.be/lOEW-YUrAbE

[khizerk]

Thanks Amit. I'm not sure how an independent table will help since that would mean that I will have to reapply all the filters again, except the one I need removed. Other filters can change depending on the layout of the pivot table used by the user.