Avg function considering non-actualized weeks

[Subhasmita]

Hi Experts,

Need help in calculation for a certain scenario. I have two dimension Quarter and Week. For Week calculation, need to show sum of prod list amount, however for Quarter calculation, need to show average of Week's sum.

I have used below formula, however not getting desired result.

if(Dimensionality()=1,
Avg(Aggr(Sum([Prod List Amount])/1000000,[Fiscal Week Number])),
Sum([Prod List Amount])/1000000)

From following table, i'm able to achieve column 2 which is not correct(above formula considers the weeks which are not yet actualized for quarter), however need to have Column3 result(it should consider for average, only 2 weeks). Please help.

Column1	Coulmn2	Column3
Q1-2021	9.23	60
Week1	70	70
Week2	50	50
Week3	0	0
Week4	0	0
Week5	0	0
Week6	0	0
Week7	0	0
Week8	0	0
Week9	0	0
Week10	0	0
Week11	0	0
Week12	0	0
Week13	0	0

Thanks,

Subha

[PriyankaShivhare · Accepted solution]

U need to exclude the weeks where the sum of Amount is zero
if(Dimensionality()=1,
Avg({<[Fiscal Week Number]={"=Sum([Prod List Amount])>0"}>}Aggr(Sum([Prod List Amount])/1000000,[Fiscal Week Number])),
Sum([Prod List Amount])/1000000)

Thanks,
Priyanka

[Subhasmita]

that made the trick!

thanks a lot! 🙂