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
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)])
Hi
Can you please send us the screenshot of the data and what should be output.
Thanks.
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,
Add a comment