cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Use ALLEXCEP but still consider applied filter

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.

Best regards,

Chris

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Use ALLEXCEP but still consider applied filter

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

Proud to be a Datanaut!

Super User

## Re: Use ALLEXCEP but still consider applied filter

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

Proud to be a Datanaut!

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 320 members 3,169 guests
Recent signins: