Hi,

I want to calculate the amount of 'fact' rows with a certain value as a
percentage of the total number of fact rows.

For example, a test cube has 5000 rows. 2500 of these rows have a
sale-status of 'Complete' or 'Closed' - designated by a surrogate key
in the fact-table.

So, my hit-rate should be 50%. In the example below I have hard-coded
the value of '2500'. What logic can I insert here to produce the
calculation ?

Case
When IsEmpty( [Measures].[Sales Count] )
Then 0
Else ( ([Measures].[Sales Count] -2500 ) /
[Measures].[Sales Count])
End

Many Thanks,
Gar.

if you have a dimension like "status"
try:
Case
When IsEmpty( [Measures].[Sales Count] )
Then 0
Else ( ([Measures].[Sales Count], Status.[Closed] ) /
[Measures].[Sales Count])
End


([Measures].[Sales Count], Status.[Closed] ) return the sales count value
but for closed only.

if you don't have a dimension "status" and don't want one...
add a new measure in the cube linked to this SQL statement:
case when StatusColumn='Closed' then 1 else 0 end
map this new calculated column to a measure like "Closed Sales" (a using a
sum aggregation or a count aggr.)
and the formula become:
When IsEmpty( [Measures].[Sales Count] )
Then 0
Else [Measures].[Closed Sales]/
[Measures].[Sales Count]
End


does this helps you?



<garrett.fitzsimons (AT) gmail (DOT) com> wrote

Jéjé,

Many Thanks - I do have a Status Dim. and all is well.

As a follow-up if wanted to expand this scenario and change the
hard-coded [Closed] to reference a set of 'Status' in the same way as
you would a sub-select how would this be achieved ?

Rgds,
Garrett.


Jéjé wrote:

if you want a "% of total" calculation (I mean % in the selected status):
or
this evaluate the % of sales count in the selected status


<garrett.fitzsimons (AT) gmail (DOT) com> wrote


Jéjé,

Many Thanks - I do have a Status Dim. and all is well.

As a follow-up if wanted to expand this scenario and change the
hard-coded [Closed] to reference a set of 'Status' in the same way as
you would a sub-select how would this be achieved ?

Rgds,
Garrett.


Jéjé wrote: