Answered by:
Percentage calculation using Calculated member
Question

Hi SSAS/MDX guru,
I have a fact table that has a customerKey, ProdcutKey and Datekey, the measure i have is unit_Price and also I have a calculated member called Avg_Unit_Price.
Please give your answers as calculated member.
1. The count of customers (distinct customers) that bought a product called 'abc' and that have Avg_Unit_Price is greater than 120.
2. Percentage of customers (distinct customers) that bought a product called 'abc' and that have Avg_Unit_Price is greater than 120.
To make it more clear, let say in 2011 there are 1000 distinct customers who bought product 'abc' but only 500 of them bought the product with vg_Unit_Price greater than 120. Thus the count will be 500 and the percentage will be 500/1000*100.
Note that when I slice by Date the count and the percentage has to change accordingly (dynamic count and dynamic percentage), because at year level there could be 1000 customers but at quarter level there could be 500 customers that bought 'abc'.
Your quick response will be much appreciated.
Thanks,
 Edited by SamiVA Thursday, December 29, 2011 2:27 PM
Thursday, December 29, 2011 2:43 AM
Answers

Hi Sami  VA,
Since there is no relationship between the Reseller fact and the customer dimension in the adventure works cube, you will not be able to get the results. You have to use the Reseller key instead of customer key for the above code.
HTH,
Ram
Ram Proposed as answer by Lola Wang Friday, December 30, 2011 7:47 AM
 Marked as answer by Challen Fu Thursday, January 5, 2012 10:03 AM
Friday, December 30, 2011 5:31 AM
All replies

Any Help? I have seen a lot of people answering very challenging MDX expressions in this forum, Please help if you could.Thursday, December 29, 2011 2:46 PM

Hi Sami VA,
I have made a sample on adventure works DB and I shall share with you the steps that i followed.
1) I added customer Key by setting the property to be a Distinct count. So this will automatically create a new measure group with only one measure called customer key with distinct count as the aggregate function.
2) Then create a calculated measure with Avg Sales Price > 120. In my example i have used the value for the measure to be > 500. I have named this measure as [Customer > 500]
CREATE MEMBER CURRENTCUBE.[Measures].[Customer >500]
AS DISTINCTCOUNT(filter([Dim Customer].[Customer Key].[Customer Key],[Measures].[Sales Amount  Fact Internet Sales] > 500)),
VISIBLE = 1 ;
3) then create one more measure which will give you the ratio as
CREATE MEMBER CURRENTCUBE.[Measures].[PercCalc]
AS [Measures].[Customer >500]/[Measures].[Customer Key Distinct Count],
FORMAT_STRING = "Percent",
VISIBLE = 1 ;
HTH,
Ram
Ram Edited by Ramakrishnan.lh Thursday, December 29, 2011 5:00 PM
Thursday, December 29, 2011 4:57 PM 
Hi Ramakrishnan,
Thank you for your help, you forgot to filter by product dimension, I mean I want to know the number of customers who bought a product called 'abc' with Avg Sales Price > 120. Can you add in your calculation to include for specific products or product category in your case?
Also, note that distinct count of CustomerKey is not the same as distinct count of CustomerID, because CustomerKey is a surrogate key so since Dimcustomer table has SCD Type2 then i have to count only CustomerID not CustomerKey, I think. Normally if you have SCD Type2 in your dimension then you will have multiple CustomerKey for the same CustomerID. correct me if i am wrong.
Thanks,
 Edited by SamiVA Thursday, December 29, 2011 5:28 PM
Thursday, December 29, 2011 5:18 PM 
Hi SamiVA,
The measure [customer]>500 would give you the number of customers who bought a product called abc with Avg Sales price >120.
If you look at the scrrenshot , for Product category "Accessories" , the distinct count of customers who had ordered the product is 15114 and there are 24 distinct customers who ordered the same product with the sales amount of > 500.
HTH,
Ram
RamThursday, December 29, 2011 5:24 PM 
Hi Ram,
Thank you again for your time, when I do that I am getting the toltal number of customrs in DimCustomer not the number of customers that has value > 120 (500 in your case). am I doing wrong somewhere? To make it clear I have about 300,000 customer numbers in DimCustomer but out of those only 20,000 customers are associalted with that particular fact table. Thus, what i was expecting is the total number of distinct count of customers in this fact table is 20,000 and of those customers X number of them bought a certain prodcut with avg value > 120.
when use your code I am getting 300,000 which I expect to be less than 20,000
do you have any idea why it is getting the total customer number of the whole dimension table? Please I need your help to get this done, I have a requirment to be submitted by tomorrow.
 Edited by SamiVA Thursday, December 29, 2011 7:20 PM
Thursday, December 29, 2011 7:19 PM 
Hi Ramakrishnan,
It is working for avg value > 120 but not working for avg value <120 , I am not sure why the less than a value is not working. It is very strange, can you try on yours? do you have any alternative. Thank you for you help and time.
Thursday, December 29, 2011 9:36 PM 
Hi SamiVA,
Its working fine in my Adventure works sa mple. Nost sure why you are not getting it. Could you post the code that you are using for the same.
Alternatively you could create one more calculated member which will subtract the Distinct count of customers who have avg price >120 from the total distinct count of customers who have purchased.
CREATE MEMBER CURRENTCUBE.[Measures].[difference]
AS [Measures].[Customer Key Distinct Count][Measures].[Customer >500],
FORMAT_STRING = "0",
VISIBLE = 1 ;
HTH,
Ram
RamFriday, December 30, 2011 3:08 AM 
Hi Ramakrishnan,
I am using similar to the following, can you use [Average Reseller Sales] in your example and run the following.
WITH MEMBER [Measures].[count]
AS
DISTINCTCOUNT
(FILTER([Dim Customer].[Customer Key].[Customer Key],[Measures].[Average Reseller Sales]< 120))
SELECT [Measures].[count] ON 0
FROM [SSAS];
 Edited by SamiVA Friday, December 30, 2011 4:57 AM
Friday, December 30, 2011 4:02 AM 
Hi Sami  VA,
Since there is no relationship between the Reseller fact and the customer dimension in the adventure works cube, you will not be able to get the results. You have to use the Reseller key instead of customer key for the above code.
HTH,
Ram
Ram Proposed as answer by Lola Wang Friday, December 30, 2011 7:47 AM
 Marked as answer by Challen Fu Thursday, January 5, 2012 10:03 AM
Friday, December 30, 2011 5:31 AM