cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
klcwgs Frequent Visitor
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.


Please can you advise the correct formula to tackle this?

 

Thanks for your help!


Best regards,

Chris

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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)

 

 

All_Except.png

 

Regards,

MFelix

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




1 REPLY 1
Super User
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)

 

 

All_Except.png

 

Regards,

MFelix

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 320 members 3,169 guests
Please welcome our newest community members: