MDX, Average pre book per year

Question

I am total new in MDX, thanks for help in advanced. I want to calculate the average selling price per book per year. I tried with the below query but it takes so long and eventually I get timeout error without any explanation where the problem was.

MDX query:

SELECT
 GENERATE
(
    [book].[Id].MEMBERS, 
    AVG(
        [book].[Id].CurrentMember * [Cube Time].[Year].Children,
        [Measures].[price]
        )
)
 ON ROWS ,
{[Cube Time].[Year].[Year]} ON  COLUMNS
FROM [DB];

Where I did go wrong

Answer 1

Welcome to MDX. Since i dont know what your cube looks like, I will base my answer on AdventureWorks cube. This is a sample provided by microsoft.

So in the example below I calculate the average for each product category, foreach year. The average is calculated in a calculated measure.

with member 
[Measures].[Average]
as
[Measures].[Internet Sales Amount]/[Measures].[Internet Order Quantity]
select 
{
[Measures].[Internet Sales Amount],[Measures].[Internet Order Quantity],[Measures].[Average]
}
on columns,
non empty
([Date].[Calendar Year].[Calendar Year],[Product].[Category].[Category])
on rows
from [Adventure Works]

Result