Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
R
maybe also
Sum([Bucket 1]) / Sum([Bucket 1] + [Bucket 2] + [Bucket 3]) * [FY 1516 Flag]
Try this:
=Sum({$<[FY 1516 Flag]={1}>}[Bucket 1]) / Sum({$<[FY 1516 Flag]={1}>}[Bucket 1] + [Bucket 2] + [Bucket 3])
maybe also
Sum([Bucket 1]) / Sum([Bucket 1] + [Bucket 2] + [Bucket 3]) * [FY 1516 Flag]