CODEWITHSUNDEEP
mdx
Richard Barraclough
Richard Barraclough

Reputation: 2984

MDX aggregate out a dimension

I'm trying to compute outcome frequencies, i.e., count divided by total.

I can't work out how to get a total in MDX.

Data looks like this.

enter image description here

The fact is just a 1 so their sum is the number of experiments with the given outcome.

It's easy in SQL

SELECT Session, Outcome, fi / N AS p
FROM (
    SELECT Session, Outcome, CAST(COUNT(*) AS float) AS fi, CAST(SUM(COUNT(*)) OVER (PARTITION BY Session) AS float) AS N GROUP BY Session, Outcome
) T

Is it possible in MDX? If so: how?

I've tried these:

CREATE MEMBER CURRENTCUBE.Measures.Experiments AS SUM([Outcomes] , Measures.[Actual Outcome]);
CREATE MEMBER CURRENTCUBE.Measures.ExperimentsA AS SUM([Outcomes].[(All)] , Measures.[Actual Outcome]);
CREATE MEMBER CURRENTCUBE.Measures.ExperimentsAM AS SUM([Outcomes].AllMembers , Measures.[Actual Outcome]);

The first and third just give (null) and the second is just the same as the existing measure -- which makes no sense.

Upvotes: 2

Views: 29

Answers (1)

Richard Barraclough
Richard Barraclough

Reputation: 2984

CREATE MEMBER CURRENTCUBE.[Measures].[Experiments]
AS
    SUM([Outcomes].[All], [Measures].[Actual Outcomes])
;

So: what is the difference between [All] and [(All)]?

Upvotes: 2

Related Questions