how to get count of Measure with out 0(zero)

Question

<p>Hi </p><p>i was trying to&nbsp;calculate average of sales, so what i did is&nbsp;i tool sum of sales and then divide it by count of sales . but what i want is i want&nbsp;to&nbsp;calculate&nbsp;average with&nbsp;getting count of sales which does not&nbsp; include 0.</p><p>i have created an measure for count and set the </p><p> </p><p>for Ex.</p><p>I have sales as 25 , 25 , 0 so, i took sum (25+25+0) / 3 this gives 16.66 as&nbsp;average but what i want is i want to get count of sales as 2 only&nbsp;i want to exclude 0 in my count and want 2 as sales count and want to get aveage as (&nbsp;(25+25+0) /2 i.e 25 .</p><p></p>

Answer 1

You can write a calculated member like this

sum([Measures].[YourMeasure])/count(filter([Dimension].[Hierachy].[Level].Members, [Measures].[YourMeasure] <> 0))

Please replace the dimension section with your SET.

---

Regards, Nighting Liu

Answer 2

Hi Nighting

Thank you for reply,

i cannot filter on any dimension selection in set , because the dim will depend on user selection from Excel.

how can i achive this in cube design , becuase i want create a measure as avg sale which is calcualtes as total sales divide by count of sales i.e count should exclude zero as sale.

this is what i am doing in calculation tab in cube designer.

i have created Sale count as Measure with usage as count of non empty values and sale is normal measure , to have Avg sales i have created Avg sales measure as calculated member in calculation tab as below,

IIF([Measures].[Sale Count]=0,NULL,([Measures].[Sale]/[Measures].[Sale Count]))

Here what i want is sale count should not inculde when it has 0 .

will usage type  count of non empty values  will exclude 0 also ?

Answer 3

OK, I understand. You need to know measures are always the baby of certain dimension, so your [Measures].[Sale Count] is an aggregated value base on dimensions. I think you can understand why it will not work.

My suggestion is:

1. Add a dimension like "EverSold"
2. Calculate EverSold base on your sale count in view/store procedure/named query.
3. Ask user to use this dimension as filter when they want to know the average sale for these ever sold produects

Hope it helps

---

Regards, Nighting Liu

Answer 4

Hi Nighting

Thank you,  this helped.