20 มีนาคม 2555 0:39<p>Hi </p><p>i was trying to calculate average of sales, so what i did is i tool sum of sales and then divide it by count of sales . but what i want is i want to calculate average with getting count of sales which does not 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 average but what i want is i want to get count of sales as 2 only i want to exclude 0 in my count and want 2 as sales count and want to get aveage as ( (25+25+0) /2 i.e 25 .</p><p></p>
20 มีนาคม 2555 1:12
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
20 มีนาคม 2555 1:38
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 ?
20 มีนาคม 2555 2:22
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:
- Add a dimension like "EverSold"
- Calculate EverSold base on your sale count in view/store procedure/named query.
- 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
- ทำเครื่องหมายเป็นคำตอบโดย srinivasR 21 มีนาคม 2555 22:41
21 มีนาคม 2555 22:41
Thank you, this helped.