CODEWITHSUNDEEP
mdx
Jasmeet Dhillon
Jasmeet Dhillon

Reputation: 1

In Cube I want to get average of a measure based on particular dimension and time period

In Cube I want to get average of a measure based on particular dimension and time period but it is returning the same result even if I apply sum, Avg or directly taking the measured value, Same Query if I run into SQL it returns 43 rows but in MDX query it returns only 1 row . as cube is aggregating the measure value Query In SQL is As 

 select Measure,[Dimension Name] from  datatable where dimension1key=1 and    countryKey=2 and regionKey=1 and channelKey=3  and timeperiod between(38,50) groupBy [Dimension Name],Measure

same in MDX if I write 

select {[Measure].[price] } on columns,non empty({[Dimension].[Dimanesion Name]}) on rows from (select {[Dimcountry].[country Name].[asia]} on columns from (select {[Dimchannel].[channel Name].[a]} on columns from (select [dimension1].[Dimension1Key].[1]} on columns from (select {[region].[region Name].[abc]} on columns from retaoauditcube))))

it returns only one row as aggregated value

Upvotes: 0

Views: 1083

Answers (2)

Jasmeet Dhillon
Jasmeet Dhillon

Reputation: 1

enter code here
With SET [ALL OTHERS] AS {   ({[Dim Timeperiod Derived].[Week Sequence].& 51]:[Dim Timeperiod Derived].[Week Sequence].&[51]},[Measures].[Price Per Pack])}member [Measures].[AVG Price] as AVG([ALL OTHERS])  MEMBER [Measures].[Current AVG Price] AS MAX(({[Dim Timeperiod Derived].[Week Sequence].&[51]:[Dim Timeperiod Derived].[Week Sequence].&[51]}),[Measures].[Price Per Pack])MEMBER [Measures].[Sum Price] AS AVG(({[Dim Timeperiod Derived].[Week Sequence].&[51]:[Dim Timeperiod Derived].[Week Sequence].&[51]}),[Measures].[Price Per Pack]) SELECT {[Measures].[Current AVG Price ],[Measures].[Sum Price] ,[Measures].[AVG Price]} ON COLUMNS ,NON EMPTY {[Dim Flavor Category].[Flavor Category RD].[Flavor Category RD].ALLMEMBERS,[Dim Flavor Category].[Flavor Category RD].[ALL OTHERS]} ON ROWS FROM (SELECT {[Dim Product Level].[Product Level Name].[FLAVOUR]} ON COLUMNS  FROM (SELECT {[Dim Brand].[Brand Name].[COCA COLA]} ON COLUMNS  FROM (SELECT {[Dim Category].[Category Name].[SPARKLING]} ON COLUMNS  FROM (SELECT {[Dim Channel].[Channel Name].[Total Channel]} ON COLUMNS  FROM (SELECT {[Dim Subchannel].[Subchannel Name].[Total]} ON COLUMNS FROM (SELECT {[Dim Subregion].[Subregion Name].[Total]} ON COLUMNS  FROM (SELECT {[Dim Region].[Region TD].[Total ID]} ON COLUMNS  FROM (SELECT {[Dim Country].[Country Name].[Indonesia]} ON COLUMNS FROM [Smartscan ASEAN]))))))))

all these queries return the same output

      Current AVG Price              Sum Price         AVG Price

COLA 604314.177978516 604314.177978516 604314.177978516

Upvotes: 0

Danylo Korostil
Danylo Korostil

Reputation: 1484

You may use the MDX AVG function:

With 
Member [Measure].[AVG Price] as
AVG(
    [Date].[Calendar].[Day].Members,
    [Measure].[Price]
)

Select 
{[Measure].[Price],[Measure].[AVG Price]} on 0,
Non Empty [Dimension].[Hierarchy].[Attribute].Members on 1
From [retaoauditcube]
Where ([Dimcountry].[country Name].[asia],[Dimchannel].[channel Name].[a],[dimension1].[Dimension1Key].[1],[region].[region Name].[abc])

Upvotes: 1

Related Questions