Forum Discussion
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 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?
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
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
7 Replies
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
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!
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
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
DarekHi 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!