Average Per Store on Weekly Data

Anonymous

Not applicable

2 years ago

Table 1:

StoreNo	Store Name
22	HHH
23	TTT
32	NNN
43	BBB
44	CCC
45	OOO

Table 2:

StoreNo	Store Name	Percentage	TxnDt
22	HHH	10%	17-10-23
22	HHH	20%	18-10-23
22	HHH	30%	19-10-23
22	HHH	40%	20-10-23
22	HHH	50%	21-10-23
22	HHH	50%	22-10-23
22	HHH	50%	23-10-23
23	TTT	10%	17-10-23
23	TTT	20%	18-10-23
23	TTT	30%	19-10-23
23	TTT	40%	20-10-23
23	TTT	60%	21-10-23
23	TTT	60%	22-10-23
23	TTT	60%	23-10-23
32	NNN	10%	17-10-23
32	NNN	20%	18-10-23
32	NNN	30%	19-10-23
32	NNN	40%	20-10-23
43	BBB	20%	18-10-23
43	BBB	20%	19-10-23
43	BBB	20%	20-10-23

Result:

Store No	Store Nam	17-10-23	18-10-23	19-10-23	20-10-23	21-10-23	22-10-23	23-10-23	Overall
22	HHH	10%	20%	30%	40%	50%	50%	50%	36%
23	TTT	10%	20%	30%	40%	60%	60%	60%	40%
32	NNN	10%	20%	30%	40%				25%
43	BBB	20%	20%	20%					20%
44	CCC
45	OOO
Total									20%

Note: We have a separate Calendar Table in the Report. (I cannot share the sample file or link here as i don’t have access to upload from company Laptop).

Query: 1) Consider Tabl 1 as Unique(Fact Table) in the report.
2) Consider Table 2 as DIM table as we have data of store which has 7 days of data(%), and few store has 4 days of data(%), and few has 3 days of data(%).
I will give Fact table data in the filter all page.

Expecting Result:
There are 3 results expected from the business.

1) When all the data is available in the filter page, we will pick Store Num 22 and 23 (Average) (36% +40%). Result will be 36+40/2 = 38%
2) When we pick Store num 22 and 32 (Average = 36+25/2 = 30.5%)
3) When we pick Store 23 and 44 (Average = 36+(NULL)/2 = 18%)

4) When we pick overall store % (36+40+25+20+NULL+NULL)/6 = 20.16%

Over all %, this should be 44% for this week but there is DIP of 5% due to null value wrongly considered.