Forum Discussion

Olia's avatar
Olia
Advocate II
8 years ago
Solved

Multi-level rolling average

Hi everyone,   I'm trying to calculate 6 months average amount per financial statement item (FSI), which is basically a multi-level thing (as you have a level Wages for example, and below that leve...
Need Help
  • jthomson's avatar
    jthomson
    8 years ago

    Probably to do with that all(sheet) at the end, see if allexcept(sheet, sheet[FSI]) does anything

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Olia You need to do ALLEXCEPT(FSI ITEM) to calculate rolling average. Also you could use variable to make it cleaner.

 

Rolling Avg 6m =

 

VAR Opt1 = 1st calc

VAR Opt2 = 2nd calc

 

RETURN IF(COUNTROWS(values('Sheet'[Month]))=1, Opt1, Opt2)

Olia's avatar
Olia
Advocate II
8 years ago

jthomson yes, now it works on month and FSI level. but it's not working on Country-level or agregate level yet. Sadly just adding ,"Sheet"[Country_code" to that allexcept formula does not seem to work. suggestions?

 

Anonymous I'm sorry, I don't understand.