Use ALLEXCEP but still consider applied filter

[klcwgs]

Fund;     Investment;     Invested

F1          Inv1                 20

F2          Inv1                 30

F3          Inv2                 40

I want to have a column: Total invested. Which i understood i can get by Calculate(Sum(Invested),Allexcept(table, investment)).

Fund;     Investment;     Invested;    Total invested

F1          Inv1                 20               50

F2          Inv1                 30               50

F3          Inv2                 40               40

However I have a filter on the page to filter Fund the fund, and if i select only F1 and F3 the desired result is

F1          Inv1                 20               20

F3          Inv2                 40               40

The formula above still gives me 50 for the total invested for F1.

Please can you advise the correct formula to tackle this?

Thanks for your help!

Best regards,

Chris

[MFelix · Accepted solution]

Hi @klcwgs,

Try this solution with ALLSELECT:

ALLSELECTED =
IF (
    COUNT ( Table1[Fund] ) = 1,
    CALCULATE (
        SUM ( Table1[Invested] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[Investement] = MAX ( Table1[Investement] )
        )
    ),
    CALCULATE ( SUM ( Table1[Invested] ), ALLSELECTED ( Table1 ) )
)

 The first part with IF is to return the total value if you need to have it in the table, think this works result is below with a slicer but also works with page filters. (ALLEXCEPT is your formula - ALLSELECT is mine)

Regards,

MFelix