Average in Calculations

[Anonymous]

I'm trying to do some Outlier analysis, but this has led to more simple things that I'm not able to do along the way.

To keep it simple, I have a table of data with a unique REF and a numeric count of Defects.

I want to compare the number of defects on each row, to the average, so I'm first calculating the average...

If I add a column : cAveDefects = average(table[Defects]), then this is calculated for the ENTIRE table (which is fine).

But my first hurdle is that this doesn't update when I filter, eg. to only thse that have 2 or less defects ?

So say I wanted to filter by some other attribute (colour) it wouldn't change either, so I'd be comparing the rows defect value with the ENTIRE table not just the filtered values.  I'm guessing I need to use CALCULATE but various attempts have failed.  Any ideas?

[amitchandak]

@Anonymous , Create a measure as suggested by @Pragati11 . The column will not change with slicer.

also check:https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

[Pragati11 · Accepted solution]

Hi @Anonymous ,

Try modiffying your DAX as follows:

cAveDefects = CALCULATE(AVERAGE(table[Defects]), ALLSELECTED(table))

See if this helps in your scenario.

Thanks,

Pragati