Set Analysis With A Calculation

rileymd88 — Fri, 15 Jan 2016 16:18:29 GMT

Hi All,

I have a problem trying to use the set analysis with a calculation include.

My Table is very simple:

Product	FY 1516 Flag	Bucket 1	Bucket 2	Bucket 3
1	0	10	20	30
2	1	15	10	10
3	1	5	5	5

I would like to make the following calculation but only when the FY 1516 Flag is Set to "1":

=Sum([Bucket 1]) / Sum([Bucket 1] + [Bucket 2] + [Bucket 3])

When I try to do this using a Set Analysis it does not work, however it works if I only try to sum up Bucket 1

Working: Sum( {$<[FY 1516 Flag]={1}>} [Bucket 1])

Not Working: Sum( {$<[FY 1516 Flag]={1}>} Sum([Bucket 1]) / Sum([Bucket 1] + [Bucket 2] + [Bucket 3]))

Not Working: Sum( {$<[FY 1516 Flag]={1}>} [Bucket 1]) / ([Bucket 1] + [Bucket 2] + [Bucket 3]))

Can anyone help?

Re: Set Analysis With A Calculation

sunny_talwar — Fri, 15 Jan 2016 18:09:25 GMT

Try this:

=Sum({$<[FY 1516 Flag]={1}>}[Bucket 1]) / Sum({$<[FY 1516 Flag]={1}>}[Bucket 1] + [Bucket 2] + [Bucket 3])

maxgro — Fri, 15 Jan 2016 18:13:36 GMT

maybe also

Sum([Bucket 1]) / Sum([Bucket 1] + [Bucket 2] + [Bucket 3]) * [FY 1516 Flag]