Hi everyone
My example is based on the Foodmart Sales Cube.

I calculated price by Saying "Sales / Units".
I then took "Price * Profit" to come to my Gross Profit.
(These are just examples)

The problem is that in the CUBE I want "Price" only to
caluculate at the lowest level and that Gross Profit must
roll up into Product/Time, etc.
But what is happening is that "Price" is rolling up to,
meaning that my Gross Profit at higher levels are wrong.

Any idea how I can fix this please

Granville

NB: New MSAS user

Hi

The point here is that MDX calculations are re-evaluated at every level of a
cube's hierarchies, they do not get ggregate along hierarchies by default.

Computing an average price (Sales/Unit) at all levels does make sense, but
if you wish this calculation to be valid only at the bottom level, you will
need to restrict your MDX expression by testing the level explicitly using a
combination of the IIF() and LEVEL, ORDINAL, NAME and/or ISLEAF() functions.

Regarding the Gross Profit calculation,I think it would be better to
calculate the values directly in the relational source for each row in your
fact table, and then aggregate using MS AS standard aggregation function
(SUM). However, if you wish to re-aggregate Gross Profit based on your MDX
calculation for the Price, you will need to write an explicit aggregation
using the MDX SUM () function, which may result in poor cube performance if
your dimensions are reasonably large.

HTH

Olivier.

"Granville" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Hi Olivier

First of all, thank you for the information. Its
appreciated.

The problem I have is that I am new to MBX and no idea to
where to even start with the code.

Do you have an example I can use or a place where I can go
to use these codes.

Granville