Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

ALL with ALLEXCEPT

Hi All,    I'm getting the wrong figure with a formula using ALL and ALLEXCEPT to create a Company Average even though I think the formula is correct... Please help!!    My table called Stock con...
all
ALLEXCEPT
Company Average
  • Stachu's avatar
    Stachu
    7 years ago

    how about just this:

    Measure 2 = 
    CALCULATE(
        [Measure 1], 
        ALLEXCEPT(
            'Calendar Date Detail', 
            'Calendar Date Detail'[relative_date_no]
        )
    )

    you may need to add other columns from the calendar table if the users are allowed to filter on them, or if they are displayed in the visual

  • Anonymous's avatar
    Anonymous
    7 years ago

    The family of ALL* functions, when used as top level functions in CALCULATE, remove filters (they do not return tables) but if you put a whole table inside them, they'll remove filters from the EXPANDED VERSIONS of the tables, not just the tables as you thought (I know you did from your code). Hence, putting ALL( Stock ) will remove all filters from all related tables (on condition that the other tables are connected to Stock in a 1:many fashion, which I think is the case here). To know how the ALL* functions work you need to know the theory of expanded tables, by the way.

     

    So, in order to get what you want you have to write:

    calculate (
    [Measure 1], -- please give a meaningful name, Measure 1 means NOTHING
    all ( Stock ), -- remove all filters
    'Calendar Date Detail' -- restore the filters on Calendar Date Datail only
)


    Best
    Darek

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Your solution calculated a different "company average" per region, as  I'm assuming it was still taking into account filters on the Branch table. I amended my formula to this (excuse any formatting issues): 

 

Measure 2 = 
CALCULATE(
    [Measure 1], 
    ALLEXCEPT(
        'Calendar Date Detail', 
        'Calendar Date Detail'[relative_date_no]
    ),
    ALLEXCEPT (
        'Branch'
    ),
)

 And now it's giving me the correct figure. I was just under the impression I'd have to specify that I want to also use ALL the lines from the Stock table as well, but it works without that. Many thanks for your help! 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Does this give you the right answer?

 

Measure 2 = 
CALCULATE(
    [Measure 1], 
    ALLEXCEPT(
        Stock,
        'Calendar Date Detail',
        Branch
    )
)

Best
D.