Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
I have a table as shown below, and I would like to calculate the average by dimension starting from the beginning of the earliest financial year to the last completed financial year (excluding the current financial year). An example is provided below.
Thank you!
Accessory | FY20 | FY21 | FY22 | FY23 | 3 Year Avg. (excl FY23) |
Desktop | 10 | 20 | 17 | 1 | 16 |
Laptop | 45 | 29 | 18 | 11 | 31 |
Mobile | 76 | 70 | 29 | 14 | 58 |
Hi @CK_WAKE,
Result:
Column_1:
=ValueList('Desktop','Laptop','Mobile')
Column_2:
IF(ValueList('Desktop','Laptop','Mobile') = 'Desktop',
ONLY({<Accessory={'Desktop'}>} FY20),
IF(ValueList('Desktop','Laptop','Mobile') = 'Laptop',
ONLY({<Accessory={'Laptop'}>} FY20),
IF(ValueList('Desktop','Laptop','Mobile') = 'Mobile',
ONLY({<Accessory={'Mobile'}>} FY20),
NULL()
)
)
)
vFY2:
IF(ValueList('Desktop','Laptop','Mobile') = 'Desktop',
ONLY({<Accessory={'Desktop'}>} FY21),
IF(ValueList('Desktop','Laptop','Mobile') = 'Laptop',
ONLY({<Accessory={'Laptop'}>} FY21),
IF(ValueList('Desktop','Laptop','Mobile') = 'Mobile',
ONLY({<Accessory={'Mobile'}>} FY21),
NULL()
)
)
)
Column_3:
Change FY20 to FY21 for vFY21 and so on. (You can create variables for each.)
Result Column - 3 Year Avg. (excluding FY23):
=Ceil(($(vFY20) + $(vFY21) + $(vFY22))/3)
******Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.********