ALL with ALLEXCEPT

[Anonymous]

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 contains the measures (a fact table only) and dimensions join to it giving the Calendar and Store detail. I've got Measure 1 that is a simple sum(Value1)/sum(Value2) calculation that's presented by Region on a chart. Region "Unknown" is filtered off. The page is filtered on the last 91 days using the Calendar's Relative Date field. 

Measure 2 attempts to create a Company Average that only takes into account the filter on the Calendar table, but ignores any other, using all available data. The formula I wrote is this: 

Measure 2= CALCULATE([Measure 1],ALL('Stock'),ALL('Branch'[Regional Manager Name]),ALLEXCEPT('Calendar Date Detail','Calendar Date Detail'[Calendar Relative Date Number]))

The figure that I'm getting however is wrong and no matter what I change it's still wrong. Any suggestions? 

[Stachu · Accepted solution]

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 · Accepted solution]

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]

Hi Darek, 

Thank you for your explanation on the expanded table, I will read up on that more. I managed to make my formula work now with the solution suggested above. Measure 1 and 2 are only due to anonymization of business sensitive data, I would never call a measure that in real llife 🙂 

Thank you for your help! 

[Stachu]

what do you want to average by? days, regions, stores, something else?

Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1	Column2
A	1
B	2.5

specifically 'Stock' and 'Branch' tables

[Anonymous]

Hi Stachu, 

I call it an average but it's basically the company total for the time period specified by the date filter, so I'd like it to dynamically re-calculate if the user changes the date range for the report, but always including all values from the Stock and Branch tables regardless of the filters applied to the chart (which will be Regional Manager <> Unknown)

Sample table for Stock: 

branch_Skey	calendarID	Value 1	Value 2
-1	20190815	500	1000
-1	20190816	752	2500
-1	20190817	150	250
65	20190818	150	300
65	20190624	800	900
56	20190625	0	0
181	20190524	500	150
96	20190525	5000	2500
31	20190526	1300	1000

Sample table for Branch: 

branch_Skey	Branch Short Description	Regional Manager
-1	Web	Unknown
65	Sheffield	Manager A
56	London	Manager B
181	Stockport	Manager C
96	Newcastle	Manager A
31	Mobile	Unknown

Sample table for Calendar: 

calendarID	relative_date_no
20190815	-3
20190816	-2
20190817	-1
20190818	0
20190624	-55
20190625	-54
20190524	-86
20190525	-85
20190526	-84

Hope this helps and thank you for your help in advance! 

[Stachu · Accepted solution]

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]

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]

Does this give you the right answer?

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

Best
D.