Hi,

I'm designing a very simple cube with the dimensions

- date (year, month)
- product group (group, sub group)
- company

and the measure

- turnover
- royalty rate (percent)

My goal is to have a calculated measure which indicates the royalty
rate on all levels correctly. Since the royalty percentage is defined
on the level of product SUB group, i need to have the average
percentage in case the user is not showing the level of product sub
group. I did the following:

1) Create calculated member "RateSum" = Sum([royalty rate])
2) Create calculated member "RateCount" = Count([royalty rate])
3) Create calculated member "RoyaltyRate" = [RateSum]/[RateCount]

So good for now. The problem I have is that there are product groups
with rate 0%, which leads to wrong average values.

In SQL i would run the expression AVG(NULLIF(Rate,0)) to deal with this
problem. Does anybody know how i can exclude certain values (in my case
the value "0") from being counted by the COUNT aggregate? Or do you
have another way how I could accomplish?

Your help is greatly appreciated!

Sincerely,
DiGa

you have to use a SQL formula like for the source column of your count
measure:

case when MyColumn > 0 then 1 else 0 end
(T-SQL syntax for SQL server)

use a sum aggregation instead-of a count.
so you'll summarize 0 & 1 values and you'll have the right count.


"DiGa" <dgasser (AT) ch (DOT) oetiker.com> wrote

Very nice idea you came up with! Thank you very much for your help!