Use ALLEXCEP but still consider applied filter

Question

Fund; &nbsp; &nbsp; Investment; &nbsp; &nbsp; InvestedF1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Inv1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20F2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Inv1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 30F3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Inv2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 40&nbsp;&nbsp;I want to have a column: Total invested. Which i understood i can get by Calculate(Sum(Invested),Allexcept(table, investment)).Fund; &nbsp; &nbsp; Investment; &nbsp; &nbsp; Invested; &nbsp; &nbsp;Total investedF1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Inv1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50F2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Inv1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 30 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50F3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Inv2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 40 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 40&nbsp;&nbsp;However I have a filter on the page to filter Fund the fund, and if i select only F1 and F3 the desired result isF1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Inv1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20F3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Inv2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 40 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 40&nbsp;The formula above still gives me 50 for the total invested for F1.Please can you advise the correct formula to tackle this?&nbsp;Thanks for your help!Best regards,Chris

mfelix · Accepted Answer

Hi&nbsp;klcwgs,&nbsp;Try this solution with ALLSELECT:&nbsp;&nbsp;ALLSELECTED =
IF (
    COUNT ( Table1[Fund] ) = 1,
    CALCULATE (
        SUM ( Table1[Invested] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[Investement] = MAX ( Table1[Investement] )
        )
    ),
    CALCULATE ( SUM ( Table1[Invested] ), ALLSELECTED ( Table1 ) )
)&nbsp;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)&nbsp;&nbsp;&nbsp;Regards,MFelix&nbsp;&nbsp;

Forum Discussion

Use ALLEXCEP but still consider applied filter

1 Reply

Related content

Bottom 10 considering repeated value.

Group By not continuously applying

RANKX Function considering slicer value

Stuck applying changes

Sum interval considering many filters