AVG MDX Function

I have been trying to solve a calculated measure using both the sum/count methods and the avg function, but neither is working for the result that I want. We are trying to come up with an average course score based on an average test score. Example: user takes two tests (test1 twice for 50% and 100% - avg = 75% test2 once for 100%) and the course average would be (75+100)/2 = 87.5%. In our MDX we are getting (50+100+100)/3 = 83.33% instead.

We do have a hierarchy set based on course-->test-->test iteration where test iteration is each individual score. The overall average works the way you would typically thing with the sum/count method, but not based on how we want it to calculate AVG(AVG(test1 scores) + AVG(Test2 scorces)). I tried to use the AVG function, but this only works at the lowest level in the hierarchy and then appears to SUM up the results as you go up into the hierarchy (avg test1 75% test 2 100% course 175% -- not sure why the AVG works that way).

Can anyone tell me if this is even possible to get the average the way I am proposing or do I need to create some type of an aggregated take will calculations in the data warehouse to reference.

Thanks.

Seems like what you need to do is to define formula at the Test level to be Avg(Test.Children, Measures.CurrentMember), where at TestIteration level it will be Sum/Count formula. This will give you the results you are looking for.|||

I really appreciate your response and it is an honor to have you reply to this thread. I am sorry to sound stupid, but how do I define the calculations at the different levels and I am a bit confused with the Measures.CurrentMember. I guess I don't see how the testiteration level formula in the hierarchy gets passed to the other formula.

Thanks in advance.

|||

Here is a piece of MDX script which should do it:

CREATE AvgGrade = Measures.SumGrade / Measures.CountGrade;

(Test.Test.Test.MEMBERS, AvgGrade) = AVG(EXISTING Test.TestIteration.TestIteration.MEMBERS, AvgGrade);

|||

Thanks again for the quick response and I really appreciate you taking the time to assist me with this calculation. I just want to make sure I am doing this correctly. Here is what I put in the script editor:

CREATE AvgScore = Measures.[Test Scores] / Measures.[Completed Test Count];

([Fact Test Instance].[Hierarchy].[Course].[Test].Members, AvgScore) =

AVG(EXISTING [Fact Test Instance].[Hierarchy].[Course].[Test].[Iteration].Members, AvgScore)

We are currently using a hierarchy called [Hierarchy] (made up of Course-->Test-->Iteration) created off of a fact called [Fact Test Instance]. When I deploy this and drilldown to the test level I get a #VALUE!, so I am assuming I am doing something incorrectly. The other levels show the AvgScore result. Is there something wrong with my expression or does it have something to do with the solve order?

|||

The [Fact Test Instance].[Hierarchy].[Course].[Test] piece looks too long - there should be 3 parts - dimension, hierarchy, level and you have four parts. Is [Fact Test Instance] name of the dimension ? Then it should be

([Fact Test Instance].[Test].[Test].Members, AvgScore) = AVG(EXISTING [Fact Test Instance].[Iteration].[Iteration].Members, AvgScore);

Or another way to write it is

([Fact Test Instance].[Hierarchy].[Test].Members, AvgScore) = AVG([Fact Test Instance].[Hierarchy].CurrentMember.Children, AvgScore);

You will also need to do something similar about Course level too.

|||

Thanks again for your response and assistance. I modified the script to be the same as your second option and I am still getting the overall average at the test level when I browse the data within the BIDS browser using the AvgScore measure and the hierarchy called [Hierarchy] within the [Fact Test Instance] dimension. I have a student that has taken two different tests. Test1 taken twice - 33.33% and 100% for AVG 66.67% ; Test2 taken once - 90% for AVG 90% and the overall average would be (33.33+100+90)/3 = 74.44% and we would still like to get (66.67+90)/2 = 78.33%.

This definitely sounds like it should work, so is there something that I am missing or how can I validate that it is actually using this calculation at this specified level?

|||

From your description it sounds like you are looking above Test level, i.e. at Course level, since you want to see aggregate of two different tests. So you will also need

(AvgScore, [Fact Test Instance].[Hierarchy].Course.MEMBERS) = AVG([Fact Test Instance].[Hierarchy].Children, AvgScore);

|||

Once again I want to thank you so much for your assistance and you are absolutely right. I put in the additional script:

([Fact Test Instance].[Hierarchy].Course.MEMBERS, AvgScore) = AVG([Fact Test Instance].[Hierarchy].Children, AvgScore);

and I am now seeing the result that we are looking for. I am assuming that I simply have to repeat this up the hierarchy to keep this working this way. I really appreciate you taking the time to help me out with this issue.

AVG MDX Function

I have been trying to solve a calculated measure using both the sum/count methods and the avg function, but neither is working for the result that I want. We are trying to come up with an average course score based on an average test score. Example: user takes two tests (test1 twice for 50% and 100% - avg = 75% test2 once for 100%) and the course average would be (75+100)/2 = 87.5%. In our MDX we are getting (50+100+100)/3 = 83.33% instead.

We do have a hierarchy set based on course-->test-->test iteration where test iteration is each individual score. The overall average works the way you would typically thing with the sum/count method, but not based on how we want it to calculate AVG(AVG(test1 scores) + AVG(Test2 scorces)). I tried to use the AVG function, but this only works at the lowest level in the hierarchy and then appears to SUM up the results as you go up into the hierarchy (avg test1 75% test 2 100% course 175% -- not sure why the AVG works that way).

Can anyone tell me if this is even possible to get the average the way I am proposing or do I need to create some type of an aggregated take will calculations in the data warehouse to reference.

Thanks.

Seems like what you need to do is to define formula at the Test level to be Avg(Test.Children, Measures.CurrentMember), where at TestIteration level it will be Sum/Count formula. This will give you the results you are looking for.|||

I really appreciate your response and it is an honor to have you reply to this thread. I am sorry to sound stupid, but how do I define the calculations at the different levels and I am a bit confused with the Measures.CurrentMember. I guess I don't see how the testiteration level formula in the hierarchy gets passed to the other formula.

Thanks in advance.

|||

Here is a piece of MDX script which should do it:

CREATE AvgGrade = Measures.SumGrade / Measures.CountGrade;

(Test.Test.Test.MEMBERS, AvgGrade) = AVG(EXISTING Test.TestIteration.TestIteration.MEMBERS, AvgGrade);

|||

Thanks again for the quick response and I really appreciate you taking the time to assist me with this calculation. I just want to make sure I am doing this correctly. Here is what I put in the script editor:

CREATE AvgScore = Measures.[Test Scores] / Measures.[Completed Test Count];

([Fact Test Instance].[Hierarchy].[Course].[Test].Members, AvgScore) =

AVG(EXISTING [Fact Test Instance].[Hierarchy].[Course].[Test].[Iteration].Members, AvgScore)

We are currently using a hierarchy called [Hierarchy] (made up of Course-->Test-->Iteration) created off of a fact called [Fact Test Instance]. When I deploy this and drilldown to the test level I get a #VALUE!, so I am assuming I am doing something incorrectly. The other levels show the AvgScore result. Is there something wrong with my expression or does it have something to do with the solve order?

|||

The [Fact Test Instance].[Hierarchy].[Course].[Test] piece looks too long - there should be 3 parts - dimension, hierarchy, level and you have four parts. Is [Fact Test Instance] name of the dimension ? Then it should be

([Fact Test Instance].[Test].[Test].Members, AvgScore) = AVG(EXISTING [Fact Test Instance].[Iteration].[Iteration].Members, AvgScore);

Or another way to write it is

([Fact Test Instance].[Hierarchy].[Test].Members, AvgScore) = AVG([Fact Test Instance].[Hierarchy].CurrentMember.Children, AvgScore);

You will also need to do something similar about Course level too.

|||

Thanks again for your response and assistance. I modified the script to be the same as your second option and I am still getting the overall average at the test level when I browse the data within the BIDS browser using the AvgScore measure and the hierarchy called [Hierarchy] within the [Fact Test Instance] dimension. I have a student that has taken two different tests. Test1 taken twice - 33.33% and 100% for AVG 66.67% ; Test2 taken once - 90% for AVG 90% and the overall average would be (33.33+100+90)/3 = 74.44% and we would still like to get (66.67+90)/2 = 78.33%.

This definitely sounds like it should work, so is there something that I am missing or how can I validate that it is actually using this calculation at this specified level?

|||

From your description it sounds like you are looking above Test level, i.e. at Course level, since you want to see aggregate of two different tests. So you will also need

(AvgScore, [Fact Test Instance].[Hierarchy].Course.MEMBERS) = AVG([Fact Test Instance].[Hierarchy].Children, AvgScore);

|||

Once again I want to thank you so much for your assistance and you are absolutely right. I put in the additional script:

([Fact Test Instance].[Hierarchy].Course.MEMBERS, AvgScore) = AVG([Fact Test Instance].[Hierarchy].Children, AvgScore);

and I am now seeing the result that we are looking for. I am assuming that I simply have to repeat this up the hierarchy to keep this working this way. I really appreciate you taking the time to help me out with this issue.

Averaging on Multiple Levels.

Hello,

I am extremely new at MDX and Analysis Services.

I have read through chapter 7 of the fast track to MDX.It is a wonderful book.But it talks a lot about sales / time data.I know this is the way 99% of people will use cubes.I am trying to use a cube for another purpose and am having a hard time moving concepts on how we do things.I am the IT person for a Truss Plant in Northern Wisconsin.

This business is a job shop, 99% of the production is custom built trusses. Quoting the jobs for this type of business is always a struggle I would like to provide valuable information to management on how much it costs to build the trusses.I know this is an almost impossible task as the rules change constantly, but I believe we can use analysis services to help us find patterns in the data.I am hoping it has the capability to average measures on multiple levels without having to know what levels we want beforehand.

We have been collecting production times for about a year now. We don’t know exactly what we are looking for as of yet, but we have some ideas.I am thinking we need to start with pieces completed per man hour. But I would like to see how this changes based on all kinds of different dimensions (Truss Properties).

We have a measure called run_time_per_piece (Lumber pieces in the truss) . I would like to analyze what the average is based on different combinations of dimensions / hierarchies.I have tried using the standard AverageofChildren, but this is not working as I would think it should.I was expecting it to Average all returned values at each level no matter what the level is.One issue here is I don’t know what the hierarchies are going to be ahead of time. We only have a one measure we are looking at, at any one time but we don’t know what the hierarchies could be.

I would appreciate it if anyone could point me in the right direction. Am I using the right product for what I am trying to do?If so, how would I write the MDX statement to get the cube to average on all levels correctly.

Thanks in Advance.

Leo

Hi Leo,

If you browse the "Average Sales Amount" calculated measure in the Adventure Works cube, does it behave as you want, across hierarchies and levels? It is defined as:

Create Member CurrentCube.[Measures].[Average Sales Amount]

As [Measures].[Sales Amount]

/

[Measures].[Order Count],

Format_String = "Currency" ;

This is a common pattern for computing averages which work across all hierarchies, so it may help.

Averages with multi-select

I am trying to write an MDX calculation that calculates the average policy premium across multiple policies. It works fine when selecting one carrier. However, the avg function is basically based on the count and sum functions (at least that is what I read). When you select one carrier the average is based on the following:

Policy A: 2000

Policy B: 1000

Policy C: 3000

Average: 2000

Lets say I multi-select another carrier and the second carrier has the exact same amounts for the policies

the total policy amounts would be :

Policy A: 4000

Policy B: 2000

Policy C: 6000

Average: 2000

The summation (12000) should be divided by the number of policies * the number of carriers (6).

However, I wind up getting 4000 as my average. Any thoughts?

Here is the MDX I am using:

Total Policy Premiums:

sum(existing [POLICY].[POLICY #],([POLICY].[VEHICLE #].&[1],[measures].[TOTAL POLICY PREM]))

Avg:

avg(existing [POLICY].[Policy].children,[Measures].[Total Policy Premiums])

Any insight would be greatly appreciated.

You could do this for example:

AVG ( Existing CrossJoin( [Carrier].[Carrier].members, [Policy].[Policy #].members ),

[Measures].[TOTAL POLICY PREM] )

Average can be computed in multiple ways, so you need to think through on what exactly you are computing. In the MDX above, combinations of carrier and policy that do not exist or are null will not be included in the computation.

Average Over Time

Hi all,

I'm new to MDX and trying to accomplish the following.

I have a measure named [Bezetting] which comes from the following fact table:

OC_Date

OC_Category

OC_Number

I have 2 dimensions: Categories and Time.

Now I want to have a average over time measure like the one which is included in Enterprise edition (we only have the standard edition). I read you can do that with a calculated member.

I tried like this:

CALCULATE;

CREATE member CURRENTCUBE.[MEASURES].[GemBez] AS

AVG(Descendants([Time].CurrentMember,[Time].Levels.Count-[Time].CurrentMember.Level.Ordinal,LEAVES), Measures.[Bezetting])

But that give me VALUE! instead of the averages in the browser.

Can anyone help me out?

TIA!!!

Stijn Verrept.

Try This:

Code Snippet

WITH MEMBER [Measure].[MyAVG1] AS

'AVG(

YTD([Time].[CurrentMember],

[Measure].[Bezetting]

)'

OR

Code Snippet

WITH MEMBER [Measure].[MyAVG2] AS

'AVG(

YTD(),

[Measures].[Bezetting]

)'

Helped?

Regards!|||helped?|||

No PedroCGD,

I tried your solution but the results I got were no averages.

I finally solved it like this: I first created a daycount measure:

Code Snippet

Count(Descendants([Time].[Year - Quarter - Month - Date], [Time].[Year - Quarter - Month - Date].[Date]))

And then the average of bezetting got easy:

Code Snippet

IIF([Measures].[Bezetting] = 0,NULL,

[Measures].[Bezetting]/[Measures].[DayCount])

Thanks for your reply though!|||

I'm happy you get it!!

Mark your question as resolved to other people know!

Regards!!!

See you!

|||

The parameters to the descendants call do not look valid. The first call to .currentMember is missing a hierarchy reference and the second parameter to Level.Ordinal is redundant when using the leaves flag.

If you had a "Calendar" hierarchy it should look something like this:

CREATE member CURRENTCUBE.[MEASURES].[GemBez] AS

AVG(Descendants([Time].Calendar.CurrentMember,,LEAVES), Measures.[Bezetting])

|||

Dear Darren,

Thanks for the reply, tried your solution but I got the same values as Pedro's. So maybe Pedro's solution was correct after all, I'll check out some more tomorrow, 3am here, gotta go to bed, not thinking clear anymore

Edit: ok checked some more. Solution of Pedro is giving me other values back. Your solution gives me the same, I also adjusted the DayCount script like this:

Code Snippet

Count(Descendants([Time].[Year - Quarter - Month - Date],, leaves))

According to your example (I'm new at MDX) and it still works the same so I'll leave it like this, looks better! I use the daycount measure in other calculations as well.

|||

If you use the DayCount measure in other places you might get some performance benefits from the caching that SSAS does, so this probably a good solution.

Note that you may see a difference between

Measure / DayCount

and

Avg( <setOfDays>, Measure)

Because the Avg function will exclude days where there is not value for the measure, hence dividing the sum of the measure by a lower value.

Pedro's YTD() calculation should return the same value if you are selecting a year, but it should be different if you pick something like "Quarter 2" or a specific month as the YTD function will always return a set of members starting from the first member in a given year.

|||Great! Thanks for info, really usefull. Yes I was trying quarters and months as well.

Average Over Time

Hi all,

I'm new to MDX and trying to accomplish the following.

I have a measure named [Bezetting] which comes from the following fact table:

OC_Date

OC_Category

OC_Number

I have 2 dimensions: Categories and Time.

Now I want to have a average over time measure like the one which is included in Enterprise edition (we only have the standard edition). I read you can do that with a calculated member.

I tried like this:

CALCULATE;

CREATE member CURRENTCUBE.[MEASURES].[GemBez] AS

AVG(Descendants([Time].CurrentMember,[Time].Levels.Count-[Time].CurrentMember.Level.Ordinal,LEAVES), Measures.[Bezetting])

But that give me VALUE! instead of the averages in the browser.

Can anyone help me out?

TIA!!!

Stijn Verrept.

Try This:

Code Snippet

WITH MEMBER [Measure].[MyAVG1] AS

'AVG(

YTD([Time].[CurrentMember],

[Measure].[Bezetting]

)'

OR

Code Snippet

WITH MEMBER [Measure].[MyAVG2] AS

'AVG(

YTD(),

[Measures].[Bezetting]

)'

Helped?

Regards!|||helped?|||

No PedroCGD,

I tried your solution but the results I got were no averages.

I finally solved it like this: I first created a daycount measure:

Code Snippet

Count(Descendants([Time].[Year - Quarter - Month - Date], [Time].[Year - Quarter - Month - Date].[Date]))

And then the average of bezetting got easy:

Code Snippet

IIF([Measures].[Bezetting] = 0,NULL,

[Measures].[Bezetting]/[Measures].[DayCount])

Thanks for your reply though!|||

I'm happy you get it!!

Mark your question as resolved to other people know!

Regards!!!

See you!

|||

The parameters to the descendants call do not look valid. The first call to .currentMember is missing a hierarchy reference and the second parameter to Level.Ordinal is redundant when using the leaves flag.

If you had a "Calendar" hierarchy it should look something like this:

CREATE member CURRENTCUBE.[MEASURES].[GemBez] AS

AVG(Descendants([Time].Calendar.CurrentMember,,LEAVES), Measures.[Bezetting])

|||

Dear Darren,

Thanks for the reply, tried your solution but I got the same values as Pedro's. So maybe Pedro's solution was correct after all, I'll check out some more tomorrow, 3am here, gotta go to bed, not thinking clear anymore

Edit: ok checked some more. Solution of Pedro is giving me other values back. Your solution gives me the same, I also adjusted the DayCount script like this:

Code Snippet

Count(Descendants([Time].[Year - Quarter - Month - Date],, leaves))

According to your example (I'm new at MDX) and it still works the same so I'll leave it like this, looks better! I use the daycount measure in other calculations as well.

|||

If you use the DayCount measure in other places you might get some performance benefits from the caching that SSAS does, so this probably a good solution.

Note that you may see a difference between

Measure / DayCount

and

Avg( <setOfDays>, Measure)

Because the Avg function will exclude days where there is not value for the measure, hence dividing the sum of the measure by a lower value.

Pedro's YTD() calculation should return the same value if you are selecting a year, but it should be different if you pick something like "Quarter 2" or a specific month as the YTD function will always return a set of members starting from the first member in a given year.

|||Great! Thanks for info, really usefull. Yes I was trying quarters and months as well.

Average In MDX

Hi, Can anyone help me, I'm new in Analysis Services 2005,

how can I get the average, I did not understand the AVG function sintax

any suggestions?

Thanks.

Erika :)

Erika, have you looked at the AVG() function's description and usage examples at: http://msdn2.microsoft.com/en-us/library/ms146067.aspx?

--Artur

|||

Thank you Artur, yes I have looked these examples, I

have this code, and y run it in SQL Management Studio in the mdx query, and I get the right result but, how you

can put this code as a Calculated Member in Analysis Services 2005

WITH MEMBER AvgAge AS
'[Measures].[Age]/[Measures].[AgeCount]'
SELECT {AvgAge} ON 0,
{[Sex].[Sex].Members} ON 1
FROM PCPCube

The error says, incorrect syntax near WITH MEMBer, so what It's wrong.

Thanks

Erika :)

|||Hello Erika,

you have to put your calculation '[Measures].[Age]/[Measures].[AgeCount]' only into the expression field and the 'AvgAge' into the name field of the new Calculated Member.

Kind Regards,

Christian