Weighted Product Distribution %
Good morning,
I hope that someone can assist us with calculating a product's weighted distribution in Pyramid.
Weighted Distribution % = The Total Value of the Customers that buy a Product (where Sales <> 0 ) divide by the Total Value of All Customers (where Sales <> 0) within the Market.
Weighted distribution % = Value (Buying Customers) / Total Value (ALL Customers Sales)
Using the Sample Demo Database as an example- here is the model structure. I'm using Surname as Customer. (.pie file attached)
To showcase what we need - I will do an example using 1 Product, but we would need to have Product on Rows and show the Weighted Distribution % for all Products.
The Total Value for All Customers = 13 333 694
Total Value of the Customer's that have sales for the Product (Touring-3000 Yellow 50) = 297 043
Weighted Distribution % = 297 043 / 13 333 694 = 2.23 %
The denominator is easy to calculate as it's the Sales Value fixed to All products.
The numerator is more challenging as for each product, you need to determine the customers that bought that product, then get the Total Value for those customers.
How would you approach calculating this?
3 replies
-
These are the following steps. You can use point-and-click to do this if you are happy using the drop down like your image above. If you want the products on the rows, then you need to use script mode, because the UI won't let me tweak the current member for product.
1. Create a list in Formulate for the "last name" customers. The trick here is to use the NonEmpty Formula, which finds all customers that had sales for the given product.
{ NonEmpty({AllMembers([customers].[LastName])}, CrossJoin({[measures].[transactions Sales]}, [products].[Product].currentmember) ) }2. Create a new formula in Formulate, attach as a measure. This aggregates sales for our list of customers from before, irrespective of the product ("All products").
Aggregate( {[customers].[LastName].*[<< your list from #1 above goes here >>]}, (AllMembers([products].[Product]),[measures].[transactions Sales]) )3. Test this out. Drop your list of products in rows and use your new formula. It will be slow, since you are doing a massive recursive lookup for EACH product.
4. If you like what you see, create another formula using #2. attach as a measure as well.
([measures].+[<< you formula goes here >>])/(AllMembers([products].[Product]),[measures].[transactions Sales]) -
Thanks - we are testing implementing this, will feedback next week.
-
Hi Natalie
We have a method that works for us for WD calculation.
We calculate the denominator and the numerator separately.
The denominator is a data point with Date_Key (lowest time-level data point) and Store_Key (lowest store level data and what you refer to as customer in your example)
In addition all the columns we want to be able to calculate the WD for (item, brand, manufacturer etc) and value sales (million nis inf).
For the numerator we use 2 steps:
1. The same date point as in denominator, but only for active stores and without Store_key in the data point. it's calculated this way
2. Then, we sum this data point for all the stores
This way we show the sales for all the stores that include the data point.
The final calculation comes as division of numerator by denominator and works great within discover.
The pie file is attached for your reference (not sure it would work, given you don't have access to the data source).
Thanks