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.