-1

# Formula Giving NotAvailable

Hello, I have a requirement to have 3 columns

1. Sector(Grouping is done here on the original sector object which is Query1.Sector)
2. Measure (%CY Total Exp vs Allot which needs to be further filtered on fund value between the range 001-099)
3.Measure (%CY Total Exp vs Allot but here it needs to be further filtered on fund value between the range 100-199 or 300-399)

So i added the sector and for the first measure with range 001-099 i used this formula -: =Average(([ % CY Tot. Exp vs CY Real Allotment])ForEach([Fund - Key (Not Compounded)])ForEach([Query 1].[Sector])Where([Fund - Key (Not Compounded)]Between("001";"099")))
but it gives me #Unavailable.

for the second measure with range 100-199 or 300-399 i still wonder how to frame the formula.

Kindly help

10|10000 characters needed characters exceeded

### Related questions

Posted on Oct 21, 2019 at 10:15 AM

Hi try this formula,

=Average(([ % CY Tot. Exp vs CY Real Allotment] Where([Fund - Key (Not Compounded)]Between("001";"099")))ForEach([Query 1].[Sector]) in ([Fund - Key (Not Compounded)])

=Average(([ % CY Tot. Exp vs CY Real Allotment] Where([Fund - Key (Not Compounded)]Between("100";"199") or [Fund - Key (Not Compounded)]Between("300";"399")))ForEach([Query 1].[Sector])in ([Fund - Key (Not Compounded)])

10|10000 characters needed characters exceeded
• these two formulas actually worked. There was some issue at the BW end due to which i was not getting the values. Thanks a lot

• Posted on Oct 18, 2019 at 10:37 AM

Hi

Can you please send us the screenshot of the data and what should be output.

Thanks.

10|10000 characters needed characters exceeded
• This is not an answer to the question, but a comment. Please post as comment to the question next time. Thanks!

• Posted on Oct 21, 2019 at 02:32 PM

Hi,

we need to understand the relation between the sector and the [Fund - Key (Not Compounded))

can you please drag and drop the [Fund - Key (Not Compounded)) next to the sector.

thanks,

untitled.png (3.5 kB)