Help: Sum(value/ Count Distinct)

[#1]

I cant figure this out, what am i doing wrong?

When i do it like this i have no problems, i get the right numbers,  the 11 is based count of the months.

sum(value)/sum(numbers/11))

But when i try it like this based on the months i have selected its not working:

sum(value)/sum(numbers/count(DISTINCT monthnumber))

anyone?

[Chanty4u]

can share ur sample app?

r u trying in chart  or text object?

[Gysbert_Wassenaar · Accepted solution]

You're nesting aggregation functions. That's possible but then you need to specify which dimensions need to be used to create a list of counts of monthnumbers. You can use the aggr function for that:

sum(value)/sum(numbers/aggr(count(DISTINCT monthnumber),MyDim1, MyDim2, ... MyDimN))

[sunny_talwar]

May be this:

Sum(value)/(Sum(numbers)/Count(DISTINCT monthnumber))

[Ireneusz_Łomzik]

use aggregation function

[#6]

‌pivot table

ill add a sample soon

[#7]

i tried this, its giving me the wrong output

‌

[#8]

this is how it looks like:

number:

sum(number)/count(DISTINCT monthnumber)

total value:

sum(value)

im trying to calculate price just like the "number" expression:

its only working like this > sum(value)/sum(numbers/11))

the expressions gives me an error when i put it like this>

sum(value)/sum(numbers/count(DISTINCT monthnumber))

i wanna use the count(distinct monthnumber) function to calculate the price based on YTD