Forum Discussion

melanie-g's avatar
melanie-g
Frequent Visitor
4 years ago
Solved

Allexcept not working

Hi there,

 

I have 2 models where my ALLEXCEPT calculation works in one but not the other. 

 

Screen shots below of the model that does not work. 

 

See below my calculation....

 

For your reference:

eeCO_Desc - renamed to Company

eeBR_Desc - renamed to Dealership Location

eeDP_Desc - renamed to Deparment

eeFR_Desc - renamed to Franchise

 

 

I want this to filter down to the Franchise level which it doesn't appear to do.

 

Please refer to the yellow highlights...

The "New" line shows MTD units of 29,697, however the result from the "Check" formula is 1320659 (where I want it to show 29,697)

Similarly, the "Subaru" line shows MTD units of 9,169 and the "Check" formula produces 283654 (where I want it to show 9,169) etc...

 

I have used the same logic from my other model that works correctly, but for some reason it doesn't work here.

 

Any help would be appreciated.

Thank you.

 

 

 

 

 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, melanie-g ;

    You can replace Allexcept() with Filter(allselected('table'),[group]=max('table'[group]))

    Check =
CALCULATE (
    SUM ( [MTD] ),
    FILTER (
        ALLSELECTED ( 'table' ),
        [eeCO_Desc] = MAX ( [eeCO_Desc] )
            && [eeBR_Desc] = MAX ( [eeBR_Desc] )
            && [eeDP_Desc] = MAX ( [eeDP_Desc] )
            && [eeFR_Desc] = MAX ( [eeFR_Desc] )
    )
)

     
    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, melanie-g ;

    You can replace Allexcept() with Filter(allselected('table'),[group]=max('table'[group]))

    Check =
CALCULATE (
    SUM ( [MTD] ),
    FILTER (
        ALLSELECTED ( 'table' ),
        [eeCO_Desc] = MAX ( [eeCO_Desc] )
            && [eeBR_Desc] = MAX ( [eeBR_Desc] )
            && [eeDP_Desc] = MAX ( [eeDP_Desc] )
            && [eeFR_Desc] = MAX ( [eeFR_Desc] )
    )
)

     
    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.