I'm trying to calculate a variety of statistical calculation across a range of data such as Standard Deviation and Average.
The data comes from many different "Data sources". These are are held in a Dimension.
I tried using
STDDEVP function as shown in this example which operates on a measure.
But the results were wrong. I assume it's wrong because it's working on totals rather than raw data. [Sum of Values] is a standard aggregate measure summing the data in the fact table.
Following the link supplied by Greg I am now using this:
CREATE MEMBER CURRENTCUBE.[Measures].[Standard Deviation] AS (([Measures].[Sum of Values]^2 - (([Measures].[Sum of Values]^2)/[Measures].[Count of Values])/[Measures].[Count of Values])^0.5), FORMAT_STRING = "#,##0.00;-#,##0.00", VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact TS Data';
Which gives me this:
Which still isn't correct.
Using SQL If I calculate
STDDEV for Air Temp with the following query:
SELECT stdevp([Value]) FROM [EnvironmentalDataMart].[DDS_HILLTOP].[factTimeSeries] where [DETL_DataSourceSurrogateKey] = 78
I get: 6.13770334742149
How do to get the MDX query to calculate a
STDDEV per Datasource in my Dimension?
I've also tried using a Measure with "No aggregation" but it didn't return anything.