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, 

 

Answer 1

Any Help? I have seen a lot of people answering very challenging MDX expressions in this forum, Please help if you could.

Answer 2

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

Answer 3

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,

 

 

 

Answer 4

Hi Sami-VA,

 

  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

---

Ram

Answer 5

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.

Answer 6

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.

Answer 7

Hi Sami-VA,

 

    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

---

Ram

Answer 8

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];

 

Answer 9

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