Filter to not apply to a specific column in a table

[gmasta1129]

Hello,

I created a column using the formula below to sum the profit and loss by master portfolio code.

I added a filter on the table (filters on this visual) and it affects the column I created.

Instead of summing it, it goes back to counting row by row.  

Instead of ALLSELECTED, would ALLEXCEPT work? And if so, how do I change the formula? 

Net FX P&L = CALCULATE (
SUM ( 'daily_report'[profit/loss]),
FILTER (
ALLSELECTED ( 'daily_report' ),
'daily_report'[master portfolio id]
= SELECTEDVALUE ( 'daily_report'[master portfolio id] )
))

Table Data

Port. Code    Master Port Code    Profit and Loss      Net FX P&L

11111              11111                      50                            130

11112              11111                      80                            130

[lukiz84]

Hi, you can't apply a slicer to a calculated column. Calculated columns are calculated during refresh and don't respond to any filter context set afterwards.

[gmasta1129]

Sorry, I should have stated in the beginning that this is a measure and not a column

[Anonymous · Accepted solution]

Hi @gmasta1129 ,

Please try this code to create a measure.

Net FX P&L =
CALCULATE (
    SUM ( 'daily_report'[profit/loss] ),
    ALLEXCEPT (
        'daily_report',
        'daily_report'[master portfolio id],
        'daily_report'[Run Date]
    )
)

Best Regards,
Rico Zhou

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

[gmasta1129]

@lukiz84  thank you for the quick response.  That almost worked but I should mentioned that  I do have a slicer on the page titled "Run Date".  I would need this to apply. I dont need the other filter in the side pane under filters to apply.   The formula above is now summing all values for over 1 years worth of data.  Is there a way we can add the Run Date into the formula?

I included the run date as a column in the table below for reference.  

Table Data

Port. Code    Master Port Code    Profit and Loss      Net FX P&L     Run Date

11111              11111                      50                            130                10/18/2022

11112              11111                      80                            130                10/18/2022

[lukiz84]

Net FX P&L = 
   CALCULATE(
       SUM('daily_report'[profit/loss]),
       ALLEXCEPT('daily_report', 'daily_report'[master portfolio id])
   )