4 Replies Latest reply: Sep 24, 2018 4:45 AM by Ivan Bozov

# Average of multiple sum % calculations different months

Dear all,

I need to take the average of sums of different months:

Sum 1:

(Sum({<[Year Month]={'\$(Current_Month_YYYYMM)'}>}[Cash Amt USD])

/

Sum({<[Year Month]={'\$(Current_Month_YYYYMM)'},[Type Code]={'INV'}>}total <[Year Month]>[Amt USD]))

Sum 2:

(Sum({<[Year Month]={'\$(Prior_Month_YYYYMM)'}>}[Cash Amt USD])

/

Sum({<[Year Month]={'\$(Prior_Month_YYYYMM)'},[Type Code]={'INV'}>}total <[Year Month]>[Amt USD]))

Sum 3:

(Sum({<[Year Month]={'\$(Prior_Quarter_YYYYMM)'}>}[Cash Amt USD])

/

Sum({<[Year Month]={'\$(Prior_Quarter_YYYYMM)'},[Type Code]={'INV'}>}total <[Year Month]>[Amt USD]))

Sum 4:

(Sum({<[Year Month]={'\$(Prior_Year_YYYYMM)'}>}[Cash Amt USD])

/

Sum({<[Year Month]={'\$(Prior_Year_YYYYMM)'},[Type Code]={'INV'}>}total <[Year Month]>[Amt USD]))

I tried:

Avg(

(Sum({<[Year Month]={'\$(Current_Month_YYYYMM)'}>}[Cash Amt USD])

/

Sum({<[Year Month]={'\$(Current_Month_YYYYMM)'},[Type Code]={'INV'}>}total <[Year Month]>[Amt USD]))

+

(Sum({<[Year Month]={'\$(Prior_Month_YYYYMM)'}>}[Cash Amt USD])

/

Sum({<[Year Month]={'\$(Prior_Month_YYYYMM)'},[Type Code]={'INV'}>}total <[Year Month]>[Amt USD]))

+

(Sum({<[Year Month]={'\$(Prior_Quarter_YYYYMM)'}>}[Cash Amt USD])

/

Sum({<[Year Month]={'\$(Prior_Quarter_YYYYMM)'},[Type Code]={'INV'}>}total <[Year Month]>[Amt USD]))

+

(Sum({<[Year Month]={'\$(Prior_Year_YYYYMM)'}>}[Cash Amt USD])

/

Sum({<[Year Month]={'\$(Prior_Year_YYYYMM)'},[Type Code]={'INV'}>}total <[Year Month]>[Amt USD])))

And this resulted in an error.

Ugur

• ###### Re: Average of multiple sum % calculations different months

Nested aggregation is not allowed.

You can just divide the result of all sums by 4, or I am missing something?

• ###### Re: Average of multiple sum % calculations different months

that doesn't give me result which I would like to see also.

• ###### Re: Average of multiple sum % calculations different months

it gives me a '-'

• ###### Re: Average of multiple sum % calculations different months

What kind of average do you want to see? Maybe check this: Average – Which average?