How to use a column average as a constant for each year?

[ddasilva]

Hi - Fairly new to DAX and Measures.  I'm creating a U-chart for certain attributes.  To calculate standard deviation, I need to calculate the squareroot of the annual sample size ('Count of Value' in the below table), divided by the squareroot of the overall average value for all dates and datapoints in my model for that attribute (the '91.32' figure in the below table.  But as you can see, when I try placing the overall average into the table, it calculates the average just for that year.  This is not what I want.

I can't figure out the correct DAX formula to ensure that the average for all dates is used in the standard deviation calculation for each year.  Furthermore, when I adjust the date slicer, I still need to use the 91.32 figure.  Some users of my report will only want to view data for a certain year, but the U-chart calculation still requires the total average for the entire data set.

Any suggestions would be appreciated!

Thanks,

Derek

[sturlaws]

Hi, @ddasilva,

you need to manipulate the filter context of your measure, using calculate and all:

overall average = calculate(average(value),all(yourtablename))

cheers, 
Sturla

[ddasilva]

Thanks! I think we're getting closer.  I can now get a constant value to appear in the table, against each row for year.  Is there a way that can I isolate this total average caculation to a single column of data?  I'm using a querry to unpivot the original table.  At the moment, the average calculation is using ALL the data, Points 0, 1, 2... all the way to Point 10 (as illustated by the data slicer).  In the illustration below, '91.32' is the correct total average for all Point 0 data.  Is there a way I can isolate the average caculation to only those data points where the Attribute field = 'Point 0'?  As user select different Attributes to analyze, the corresponding total average for all dates should then change.

Appreciate the help.

-Derek

[ddasilva · Accepted solution]

I think I got it.  A changed 'ALL' to 'ALLSELECTED.'

Thank you!

[sturlaws]

In general I try to avoid ALLSELECTED, as it is quite hard to fully understand what this function does: https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/

Try using this instead:

overall average =
CALCULATE (
    AVERAGE ( [value] ),
    REMOVEFILTERS ( yourtablename ),
    VALUES ( yourtablename[Attribute] )
)