Calculating AVG Values

Hi mates,

Code Snippet

WITH
MEMBER [MEASURES].X AS AVG([MEASURES].[ITEM VALUE])
MEMBER MEASURES.PARENTID

AS [DEPARTMENT].DEPARTMENTS].CURRENTMEMBER.PARENT.NAME

MEMBER MEASURES.CHILDRENID AS
GENERATE(DESCENDANTS([DEPARTMENT].[DEPARTMENTS].CURRENTMEMBER),
[DEPARTMENT].[DEPARTMENTS].CURRENTMEMBER.NAME, " - ")
MEMBER MEASURES.CHILDRENNAME AS
GENERATE(DESCENDANTS([DEPARTMENT].[DEPARTMENT].CURRENTMEMBER),
[DEPARTMENT].[DEPARTMENT].CURRENTMEMBER.NAME, " - ")
SELECT
CROSSJOIN([TIME].YEAR.&[2006], [TIME].[DATEHIER].[MONTH].MEMBERS,
DESCENDANTS([DEPARTMENT].[2],
[DEPARTMENT].[DEPARTMENTS].CURRENTMEMBER, SELF_AND_BEFORE)) ON ROWS ,

{(MEASURES.PARENTID), (MEASURES.CHILDRENID), (MEASURES.CHILDRENNAME),

[MEASURES].X} ON COLUMNS

FROM OLAPDEV
WHERE ([ITEM].[78])

this MDX Query returns the following result

Year
Month
Department
Department ParentId
Department ChildrenIds
Department ChildrenNames
ItemValue (Measure Value)

This returns sum of Item value for each month,year and deparmemnt.

But I need Avg of Item Values for each month, year and department

please help me

thanks

anand

You have an "Item Value" measure defined. Add another measure which is the Count aggregate function instead of the Sum aggregate function. Now that you have the Sum and the Count, you can simply do Sum/Count and calculate the average. So your MDX would start like the following:

WITH
MEMBER [MEASURES].X AS IIf(IsEmpty([MEASURES].[ITEM VALUE COUNT]),null,[MEASURES].[ITEM VALUE]/[MEASURES].[ITEM VALUE COUNT])

|||

It is not returning the correct value,

could not get count for each year, month and by department.

count to be calculated , using group of year, month and department,

but by above solution i couldn't get that

|||You're going to need to give us more info if you want our help. We can't read your mind. What precisely is wrong? What is the correct value? What is currently being returned?|||

Code Snippet

WITH

MEMBER [MEASURES].X AS [MEASURES].[ITEM VALUE]

MEMBER MEASURES.PARENTID AS

[DEPARTMENT].[DEPARTMENTS].CURRENTMEMBER.PARENT.NAME MEMBER

MEASURES.CHILDRENID AS

GENERATE(DESCENDANTS([DEPARTMENT].[DEPARTMENTS].CURRENTMEMBER),

[DEPARTMENT].[DEPARTMENTS].CURRENTMEMBER.NAME, " - ")

MEMBER MEASURES.CHILDRENNAME AS

GENERATE(DESCENDANTS([DEPARTMENT].[DEPARTMENT].CURRENTMEMBER),

[DEPARTMENT].[DEPARTMENT].CURRENTMEMBER.NAME, " - ")

SELECT

CROSSJOIN([TIME].YEAR.&[2006],

[TIME].[DATEHIER].[MONTH].MEMBERS,

DESCENDANTS([DEPARTMENT].[2],

[DEPARTMENT].[DEPARTMENTS].CURRENTMEMBER, SELF_AND_BEFORE)) ON ROWS ,

{(MEASURES.PARENTID), (MEASURES.CHILDRENID),

(MEASURES.CHILDRENNAME), [MEASURES].X} ON COLUMNS

FROM IBMTEST

WHERE ([ITEM].[78])

above query returns following results, which returns sum of itemvalue group by year, month, department.

Year #Month #Department #Parent.Dept #Child.deptIDs #Child.DeptNames #Value

2006 1 2 2 2 All - Corporate 337710

2006 2 2 2 2 All - Corporate 328059

2006 3 2 2 2 All - Corporate 432346

2006 4 2 2 2 All - Corporate 342362

2006 5 2 2 2 All - Corporate (null)

my requirement is avg(itemvalue) group by year, month and department.

WITH
MEMBER [MEASURES].X AS IIf(IsEmpty([MEASURES].[ITEM VALUE COUNT]),null,[MEASURES].[ITEM VALUE]/[MEASURES].[ITEM VALUE COUNT])

if i use your above calculated member it gives error for using item [MEASURES].[ITEM VALUE COUNT],

WITH
MEMBER [MEASURES].X AS IIf(IsEmpty( COUNT([MEASURES].[ITEM VALUE])),null,[MEASURES].[ITEM VALUE]/ COUNT([MEASURES].[ITEM VALUE]))

i removed count and used count(measures.itemvalue), this is also returing the same value as sum. even using AVG(MEASURES.ITEMVLAUE) also not

working.

i think this is not working out since crossjoin is used. i hope this is more clear to u.