Measure calculation question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi all,

I am trying to perform a calculation which is causing me a headache because
I can't write MDX! :( - any help would be appreciated here.

Basically, I have the following scenario of data:

Date Product Value Qty1 Qty2
1/1/05 ProductA £10 2 100
1/1/05 ProductB £15 5 100
1/1/05 ProductC £5 4 100
1/1/05 ProductD £30 7 100
1/1/05 ProductE £40 5 100

2/1/05 ProductA £10 2 250
2/1/05 ProductB £15 5 250
2/1/05 ProductC £5 4 250
2/1/05 ProductD £30 7 250
2/1/05 ProductE £40 5 250

3/1/05 ProductA £10 2 500
3/1/05 ProductB £15 5 500
3/1/05 ProductC £5 4 500
3/1/05 ProductD £30 7 500
3/1/05 ProductE £40 5 500

I want to be able to perform the sum value/Qty2, but i only want to perfom
the division on one of the Qty2 values, plus, when i aggregate, I dont want
the values to sum, i.e. producing a value of 500 for the data for 1/1/05. It
should remain as 500 at any level of slicing and the 'Value' should always
perform the calcualtion at any level of a dimension.

Does this make sense? Sorry for the bad explanation, but i am new to AS and
have been reall thrown in at the deep end as apparently DBA's should know
everything! :)

Any help is appreciated.

Regards
Immy


.

Relevant Pages

  • Re: Employee count for HR Cube
    ... the part of the calculation where you are crossjoining and summing ... Darren Gosbell - SQL Server MVP ... I have sql 2k and i want to calculate the employee count for ... in MDX ...
    (microsoft.public.sqlserver.olap)
  • Re: Calculations
    ... 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. ... if you wish this calculation to be valid only at the bottom level, ... if you wish to re-aggregate Gross Profit based on your MDX ...
    (microsoft.public.sqlserver.olap)
  • Re: Calculations
    ... >if you wish this calculation to be valid only at the ... >need to restrict your MDX expression by testing the level ... aggregation function ... if you wish to re-aggregate Gross Profit ...
    (microsoft.public.sqlserver.olap)
  • Re: Dynamic FORMAT_STRING for Measure
    ... Anyone who has looked at the AdventureWorks sample cube will probably ... FORMAT_STRING can now be defined with an MDX expression (in AS2K it was ... For example, in the Adventure Works cube, if you add this calculation to ...
    (microsoft.public.sqlserver.olap)
  • Formula error - cannot find dimension member
    ... I need your help on resolving "Formula error - cannot find dimension ... I'm using OPENQUERY to run MDX to return data to my ASP application. ... Yet the same MDX statement works perfectly in MDX SAMPLE APPLICATION. ... measure expression like below (standard deviation calculation formula): ...
    (microsoft.public.sqlserver.olap)