ALL() function not working as expected

[jscottNRG]

Hi, I've perused the similar forum posts but I still can't get ALL() to work as I believe it should.


I'm trying to create a measure that calculates an overall average of a column -- that is, an average that is unaffected by slicers.  I've tried this:

Overall Score Average = CALCULATE(AVERAGE(Job_info[Score]), ALL(Job_info))

And also tried creating a criterion that shouldn't be relevant (Score will always be between 0-100) so that I could try using FILTER():

Overall Score Average = CALCULATE(AVERAGE(Job_info[Score]), FILTER(ALL(Job_info), Job_info[Score]<1000))

In both cases, making a change to my date slicer affects the measure's result.  Am I using ALL() incorrectly?  Thanks in advance for any thoughts.

[Anonymous · Accepted solution]

So you said it changes when you apply your date slices, is the date field in your slicer from your main table or from a separate calendar/date table. If it's from a separate table try putting the all statement around that field/table instead

[srikanthgunnam]

Hello,

I have a  matrix visual with 8 to 9 columns and have one page level filter(Values:1,2,4--Applied filter not in 4) and one report level filter(Applied filter sales not in US) applied to the report. Now, when ther user filter(Yes/No filter name) out of 8 to 9 columns, 3 columns values should not be effected in the matrix but when the user selects other filters, values should change. I have all the columns and filters coming from the same table.

Can anyone help me out in getting this?

I have tried using this

Calculate(DISTINCTCOUNT(column),All(Yes/No)) is not giving me the correct result.

[Ashish_Mathur]

Hi,

Assuming the title of the Yes/No column is response and the Table name is Data, try this

=Calculate(DISTINCTCOUNT(column),All(Data[Response]))

[srikanthgunnam]

Hello,

Thank you for your response, I just mentioned Yes/No for understanding. I did use the column name it is not working as expected when I have one more filter from the same table. Could you please help me out?

[Ashish_Mathur]

Hi,

Share some data, explain the problem and show the expected result.

[jscottNRG]

Thanks all for the assistance! @Anonymous was on the right track and prompted me to try adding ALL() filters to the CALCULATE() function for each dimension table in my data model (including my Calendar table), like this:

Overall Score Average = CALCULATE(AVERAGE(Job_info[Score]), ALL(Job_info), ALL('Calendar'), ALL(dim_1), ALL(dim_2), ALL(dim_3), ALL(dim_4), ALL(dim_5), ALL(dim_6), ALL(dim_7), ALL(dim_8), ALL(dim_9))

Now my "Overall Score Average" doesn't change when I make slicer selections in slicers using those dimension tables.

[Ashish_Mathur]

Hi,

What do you mean by - "tried creating a criterion that shouldn't be relevant (Score will always be between 0-100) so that I could try using FILTER()"?

[edhans]

I cannot get this to fail.

Total Average = CALCULATE(AVERAGE(Table1[Amount]),ALL(Table1))

That always shows the average for everything in the table for me, regardless of a date filter/slicer.

Reg Average = AVERAGE(Table1[Amount])

This changes with the slicer, as it should.

Can you post your PBIX so we can see if there is anything else going on in there? Though I cannot imagine what. You are removing the filters from the entire table, not just a column. I know if you are sorting [Score] by another column you have to remove the filter from both [Score] and the sorting field, but that shouldn't be relevant since you are using ALL(TableName) with no columns specified.