Hi,
I would like to calculate the Numerical and Weighted distribution for a retail company.
In my exemple I have
Set of data in the workbook :
Date | POS | Product | Qty Sold |
01/01/16 | POS_1 | PRD_1 | 170 |
01/01/16 | POS_1 | PRD_2 | 76 |
01/01/16 | POS_1 | PRD_4 | 99 |
01/01/16 | POS_2 | PRD_1 | 55 |
01/01/16 | POS_2 | PRD_3 | 6,25 |
01/01/16 | POS_2 | PRD_4 | 61,5 |
..... | ... | ... | ... |
Numerical distribution :
%age of presence : Number of POS where the product is sold / Number of total POS
Weighted Distribution :
Weighted %age of presence : Total sales of POS where the product is sold / Total sales
(the total sales of the POS not only the product sales)
I need also to show the presence of product over 1 month or 3 months / 6 months back.
The result should be a table like this :
2016-01 | 2016-02 | |||||||
Weighted | Numerical | Weighted 3 month back | Numerical 3 month back | Weighted | Numerical | Weighted 3 month back | Numerical 3 month back | |
PRD_1 | % | % | % | |||||
PRD_2 | % | % | … | |||||
PRD_3 | % | … | ||||||
PRD_4 | … | |||||||
PRD_5 |
One Exemple :
the Product PRD_1 is sold in 3 POS on 5
Numerical = 3/5 = 60%
Those 3 POS have a total Qty sold of 800 on 1000 for all POS
Weighted : 800/1000 = 80%
I've started a dashboard with only the numerical but it doen't work totally if I select more than 1 month.
doing :
{EXCLUDE [POS]: sum(if [Qty Sold]>0 then 1 else 0 END)} / {fixed : countd([POS])}
So far, I can't figure out my problem.
Anyone can help me on that ?
Thank you,
C.
I found a similar subject resolving my questions : Re: Weighted Distribution Calc