Autonumber field: calculated

Showing posts with label calculated. Show all posts

Sunday, February 19, 2012

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

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.

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);

|||

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)

|||

([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.

|||

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);

Thursday, February 16, 2012

Averages in Matrix report

Greetings,
I have a report that works just great:
Jan Feb Mar Apr
Facility1 80 78 65 90
Facility2 85 79 90 49
It tells you a calculated score for each facility for each month. It's
exactly what they asked for. Until I showed it to them. Now they want
more info.
So I added another row to sort by - Region
Jan Feb Mar
region1 facility1 80 78 65
facility2 85 79 90
region2 facility3 81 65 82
facility4 84 90 71
And that works great. The problem is, they want a YTD col, and these
are scores. I can't just add them up, I have to either average them,
or calc a YTD score for each facility and put it out to the right after
the last months column.
Jan Feb Mar YTD
region1 facility1 80 78 65 74.33
facility2 85 79 90 84.66
region2 facility3 81 65 82 76
facility4 84 90 71 81.66
The second problem is, I'd like to be able to see an average for
Regions. Is it possible?
Jan Feb Mar YTD
region1 facility1 80 78 65 74.33
facility2 85 79 90 84.66
region average 82.5 78.5 77.5 79.49
region2 facility3 81 65 82 76
facility4 84 90 71 81.66
region average 82.5 77.5 76.5 78.83
here's my RDL.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>Brown</BackgroundColor>
<BorderWidth>
<Bottom>3pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<FontSize>18pt</FontSize>
<TextAlign>Center</TextAlign>
<Color>White</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.33in</Height>
<Width>5in</Width>
<CanGrow>true</CanGrow>
<Value>Monthly Scorecard</Value>
</Textbox>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N0</Format>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>9pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=(Sum(Fields!MonthTotal.Value) & " %
")</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.24in</Height>
</MatrixRow>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N0</Format>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>9pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!month.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.24in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>0.96875in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>Cypress_APP</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_month">
<GroupExpressions>
<GroupExpression>=Fields!month.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!month.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<ReportItems>
<Textbox Name="month">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>=iif(RunningValue(Fields!PortfolioName.Value &
Fields!name.Value, Countdistinct, Nothing) mod 2, "LightBlue",
"White")</BackgroundColor>
<FontSize>9pt</FontSize>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>month</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=choose(Fields!month.Value,
"Jan Score",
"Feb Score",
"Mar Score",
"Apr Score",
"May Score",
"Jun Score",
"July Score",
"Aug Score",
"Sept Score",
"Oct Score",
"Nov Score",
"Dec Score"
)</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.24in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>4.90625in</Width>
<Top>0.33in</Top>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_RowGroup2">
<GroupExpressions>
<GroupExpression>=Fields!PortfolioName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>=iif(RunningValue(Fields!PortfolioName.Value &
Fields!name.Value, Countdistinct, Nothing) mod 2, "LightBlue",
"White")</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>9pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!PortfolioName.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>0.875in</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_RowGroup3">
<GroupExpressions>
<GroupExpression>=Fields!name.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>=iif(RunningValue(Fields!PortfolioName.Value &
Fields!name.Value, Countdistinct, Nothing) mod 2, "LightBlue",
"White")</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>9pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!name.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>3in</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="RowColor">
<GroupExpressions>
<GroupExpression>=1</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>=iif(RunningValue(Fields!PortfolioName.Value &
Fields!name.Value, Countdistinct, Nothing) mod 2, "LightBlue",
"White")</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>9pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=RunningValue(Fields!PortfolioName.Value &
Fields!name.Value, Countdistinct, Nothing)</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>0.03125in</Width>
</RowGrouping>
<RowGrouping>
<Width>0.03125in</Width>
<StaticRows>
<StaticRow>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>=iif(RunningValue(Fields!PortfolioName.Value &
Fields!name.Value, Countdistinct, Nothing) mod 2, "LightBlue",
"White")</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>9pt</FontSize>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Month Total</Value>
</Textbox>
</ReportItems>
</StaticRow>
<StaticRow>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>=iif(RunningValue(Fields!PortfolioName.Value &
Fields!name.Value, Countdistinct, Nothing) mod 2, "LightBlue",
"White")</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>9pt</FontSize>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</StaticRow>
</StaticRows>
</RowGrouping>
</RowGroupings>
</Matrix>
</ReportItems>
<Style />
<Height>1.05in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Cypress_APP">
<rd:DataSourceID>ed3b9625-deb2-4de7-b3be-35b5117506fa</rd:DataSourceID>
<DataSourceReference>Cypress_APP</DataSourceReference>
</DataSource>
</DataSources>
<Width>7.375in</Width>
<DataSets>
<DataSet Name="Cypress_APP">
<Fields>
<Field Name="name">
<DataField>Name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PortfolioName">
<DataField>PortfolioName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="month">
<DataField>Month</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="measureID">
<DataField>MeasureID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="MonthTotal">
<DataField>MonthTotal</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Cypress_APP</DataSourceName>
<CommandText>SELECT e.Name, Portfolios.Name AS
PortfolioName, sc.Month, sc.MeasureID, dbo.udfn_M1vsM2(CASE WHEN
sc.[MeasureID] IN (26)
THEN (CASE sc.[Measure1] WHEN 0 THEN 0 ELSE
sc.[Measure2] / sc.[Measure1] END) ELSE sc.[Measure1] END, CASE WHEN
sc.[MeasureID] IN (26)
THEN sc.[Measure4] ELSE sc.[Measure2] END,
m.BusinessRule, m.partialScore, m.Score) AS MonthTotal
FROM ScoreCard sc INNER JOIN
Enterprise e ON sc.EnterpriseID = e.EnterpriseID
INNER JOIN
Measure m ON sc.MeasureID = m.MeasureID INNER
JOIN
Enterprise_View ev ON e.EnterpriseID = ev.EnterpriseID INNER JOIN
(SELECT EnterpriseID, [Name]
FROM Enterprise
WHERE EnterpriseTypeID = 2) Portfolios
ON Portfolios.EnterpriseID = ev.ParentID
WHERE (sc.Year = YEAR(@.date1))
GROUP BY Portfolios.Name, e.Name, sc.Month, sc.MeasureID, m.MeasureID,
sc.Measure1, sc.Measure2, sc.Measure4, m.BusinessRule, m.partialScore,
m.Score
ORDER BY sc.Month, e.Name, m.MeasureID</CommandText>
<QueryParameters>
<QueryParameter Name="@.date1">
<Value>=Parameters!date1.Value</Value>
</QueryParameter>
</QueryParameters>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>1577349c-ff67-4564-9cb1-afa782e635fe</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<ReportParameters>
<ReportParameter Name="date1">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>=DateAdd(DateInterval.Day, (DatePart(DateInterval.Day,
Now)) * -1, Now)</Value>
</Values>
</DefaultValue>
<Prompt>date1</Prompt>
</ReportParameter>
</ReportParameters>
<Language>en-US</Language>
</Report>> And that works great. The problem is, they want a YTD col, and these
> are scores. I can't just add them up, I have to either average them,
> or calc a YTD score for each facility and put it out to the right after
> the last months column.
> Jan Feb Mar YTD
> region1 facility1 80 78 65 74.33
> facility2 85 79 90 84.66
> region2 facility3 81 65 82 76
> facility4 84 90 71 81.66
No problem at all. I would create the average based upon the values coming
back from your query. Depending on how your report is set up, you will need
to place logic in your code to account for the number of months. If a month
has a zero score, don't include it in your average calculation...or
something like that.
> The second problem is, I'd like to be able to see an average for
> Regions. Is it possible?
> Jan Feb Mar YTD
> region1 facility1 80 78 65 74.33
> facility2 85 79 90 84.66
> region average 82.5 78.5 77.5 79.49
> region2 facility3 81 65 82 76
> facility4 84 90 71 81.66
> region average 82.5 77.5 76.5 78.83
Depending on how you have your report set up, there are several ways to do
this. You can add up the actual report objects (textbox1, textbox2, etc) and
get your average that way (check out the RunningValue() function), or you
could write your own function in the code block that maintains the average
value per region as the report renders.|||G wrote:
> No problem at all. I would create the average based upon the values coming
> back from your query. Depending on how your report is set up, you will need
> to place logic in your code to account for the number of months. If a month
> has a zero score, don't include it in your average calculation...or
> something like that.
Yes, but how do I show it. When I click the subtotal button, it puts a
total bar out there. If I put a formula in there, it puts the forumula
in the header of the report, not on the subtotal line like I want it.
> > The second problem is, I'd like to be able to see an average for
> > Regions. Is it possible?
> >
> > Jan Feb Mar YTD
> > region1 facility1 80 78 65 74.33
> > facility2 85 79 90 84.66
> > region average 82.5 78.5 77.5 79.49
> >
> > region2 facility3 81 65 82 76
> > facility4 84 90 71 81.66
> > region average 82.5 77.5 76.5 78.83
> Depending on how you have your report set up, there are several ways to do
> this. You can add up the actual report objects (textbox1, textbox2, etc) and
> get your average that way (check out the RunningValue() function), or you
> could write your own function in the code block that maintains the average
> value per region as the report renders.
Once I have the value, how do I insert it? as another rowgroup?

averageofChildren vs avg()/count()

hi,

which is better to use: averageofchildren or avg()/count() calculated measure, if i don't want to calculate average over time.

i have read in the "Analysis Services Performance Guide" that is better to avoid using semi-additive functions to improve performance.

Special aggregate functions require unique performance-tuning techniques. They

include DistinctCount and a collection of semiadditive aggregate functions. The

semiadditive functions include: FirstChild, LastChild, FirstNonEmpty,

LastNonEmpty, ByAccount, and AverageOfChildren.

thanks

Christina

Probably the normal average (sum/count) is the better choice if you won't consider time. AverageOfChildren requires a time dimension and Enterprise Edition.|||

i have both time dimension and enterprise edition.

but my question is, i have lots of measures in which i will be using the average. i would like to know if i use averageofchildren is convenient or better use the other way, in terms of performance.

thanks

|||

Now I am not answering your question again, but.. since the average of sum/count will be a calculated member, it will take zero processing time for the calculation, but the sum and count will take processing time (both probably much faster than AverageOfChildren). However, it will take time to calculate the value when a user executes the query. AvaregeOfChildren is used for measures and therefore will take some time to process, but then zero extra time for queries (if aggregated).

How they compare (process sum+count + calculate average vs just process) I don't know, but you can probably test this on your specific system. In general using measures is better than calculated members (faster for end users, but as said, that depends on how long it takes to process the measure vs calculate the member).

Cheers

|||

Thanks HappyCow.

i think i got the point. I'll go for averageofChildren and test accordingly. if i find it slow, will try the other way.

Hope you remain always happy..but not a cow.

Cheers

Average on a calculated column in matrix

Hi,

i have some data i am showing in a matrix. The first row group is on month, the second row group is on year, in this way i get year on year reporting of my sales data.

One of the columns i have in the matrix is a "average $ per hour". The calculation for this works nicely on each row, i just get the total sales, and divide by the number of hours in that particular month (which i calculate). So it looks like this (these figures are invented):

Month Year Total sales Avg $ per hour May 2005 $123,456 $20.15 2006 $129,112 $21.56 June 2005 $100,449 $18.45 2006 $130,889 $20.57 Total $483,906 $20.10

Now i need to also put a average dollar per hour into the total row, but i cannot work out how to calculate the number of days covered by all the months listed in the matrix. Is there a way i can calculate this with an expression on the report, or will i have to resort to calculating the days per month as part of the mdx query that gathers the data?

Thanks!

sluggy

How do you calculate the number of hours in a particular month? If it is a calculated field in the dataset, you can change the expression you use for the average $ per hour column to be =Sum(Fields!Sales.Value)/Sum(Fields!Hours.Value). The value of Sum(Fields!Hours.Value) in the detail cell would be the hours for the particular month year, the value of it in the total cell should be the hours for the entire matrix.|||

Hi Fang,

the calculation to get the number of hours in a month is performed inline in the report, it is:

Sales / (24 * DateDiff("d", CDate("2006-" + Fields!Calendar_Month.Value + "01"), DateAdd("M", 1, CDate("2006-" + Fields!Calendar_Month.Value + "01"))))

(i know this doesn't allow for Feb, that is still to be fixed :) ). In the detail rows, this works nicely, but i can't see a way to implement this type of calculation in the total row.

What would be the best way to do this?

Thanks,

sluggy

|||For the total row, you'll need to sum together the hours for all the months and eliminate the duplicate periods. One way to do is through custom code. Suppose the custom function is called GetHours(), you'd call it in the expression in the total cell like this: =Sum(GetHours(Fields!Calendar_Month.Value, Fields!Calendar_Year.Value)). Inside the function, you can check the current month/year against a look-up table (which keeps track of the different periods you've seen), if it's a dup, return 0, if not, return the hours for that month.|||

Hi Fang,

thanks, that's the approach i ended up taking, here was the result (i thought i had a bug, but it was my own fault).

sluggy

average of selected members?

I got a measure caled [number of persons]

Now i want another calculated meassure which shows the average number of persons within the selected time period.

My timedimension is :

[Date].[Period].[Month]

Let's say i have

jan - 1000, feb - 1200, mar - 1100, apr - 1000

So if i select "feb" and "mar" in the cube, my average should show 1150

if i select jan,feb,apr i want my average to show 1066,67

is this possible ?

Assuming that you're using AS 2005, something like:

Avg(Existing [Date].[Period].[Month].Members, [Measures].[number of persons])

|||

Ahh yes... This will work if the selection of members is specified in the WHERE statement of the MDX query. However, it will not work if a sub-select/SUBCUBE is used.

See http://www.sqljunkies.com/WebLog/reckless/archive/2006/03/08/18601.aspx for an elaboration on the issue.

Sunday, February 19, 2012

AVG MDX Function

AVG MDX Function

Thursday, February 16, 2012

Averages in Matrix report

averageofChildren vs avg()/count()

Average on a calculated column in matrix

average of selected members?

Autonumber field

Blog Archive

About Me