Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
that made the trick!
thanks a lot! 🙂