Hello Forum,

I two dimentions:
PROJECT
TIME (days months, quarter, year)

and a simple measure
VALUE (I fill it with '1')

So if a Project was worked between 29 to 31 of May, I fill it with 3 values
for each day.

Works fine with one project, but when choosing two Projects I must get only
the value '1' for an specific day.
Example:
Prj 1 - Day29May - 1
Prj 2 - Day29May - 1
------------------------
Prj Total (Day29May) 1 <----this

I implemented it including a copy of the meassure VALUE called VALUE2
so I have:
VALUE <---agregation SUM
VALUE2 <---agregation Count

And create a calculated meassure called AVERAGE:
VALUE/VALUE2


All is fine, except when looking by the dimention TIME (for MONTH)
nth MUST SUM All those averages values,
instead of getting me the average that results in '1' at the Global.

Hope can help me,


Thanks!

--
Message posted via http://www.sqlmonster.com

Based on the example you gave, you need the number of days the selected
projects were worked. If only a single member (like month) is selected
from the TIME dimension, then:

Set [SelectedProjects] as
'{[Prj 1], [Prj 2]}'
Member [Measures].[DaysWorked] as
'Count(NonEmptyCrossJoin(
Descendants([TIME].CurrentMember,
[TIME].[Day]),
[SelectedProjects], 1))'

Select {[Measures].[DaysWorked]} on columns,
[Time].[2005].[Q2].Children on rows
from [ProjectCube]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Forget It,
I solved by myself this problem. For those want to know the answer this is:

The Fact Table Colum is called VALUE

1.- SVALUE (agregation type:Sum)
2.- CVALUE (agregation type:Count)

3.- VALUE_I_WAS_SEARCHING (SUM/COUNT)
---------------------------------
iif(isleaf([Time].currentmember),
iif(
[Measures].[CVALUE] = 0,
null,
[Measures].[SVALUE] / [Measures].[CVALUE]
),
Sum([Time].CurrentMember.children)
)

Enjoy!

--
Message posted via http://www.sqlmonster.com

Sorry Deepak,

I didn´t see You posted!
I´ll try your solution too,
thanks for your time.

Andrew

--
Message posted via http://www.sqlmonster.com

Very Good, Deepak
the same result as I have!

But how do you exclude those '0's that appeared in cells with no value?
I mean in your formula only.

Because, I used another calculated measure:
iif(daysworked = 0, null, daysworked)
to solve too.

Thanks a lot!

--
Message posted via http://www.sqlmonster.com

Your method to replace 0 with null is fine - Count() always returns a
number, so some additional logic is needed.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***