Set Analysis - whole is greater than sum of parts

[ellenblackwell]

I am using an expression like this:

sum({$<FLG={'1'}, DataSource={'A'}>}[DollarsActual])

+

(sum({$<FLG={'1'}, DataSource={'B','C'}[DollarsActual]) - Sum($<FLG={'1'}, DataSource={'B','C'}[DollarsTGT]))

When I select an individual [DataSource], I get the correct figure for each. When I select all 3 [DataSource] values, the sum is greater than the combined values for the 3 individual data sources.

For example,

I select A, and sum = 7

I select B, and sum = 3

I select C, and sum = 2

When I select no value, or I select A,B, and C, I expect to get sum = 12. But I do not; I get sum = 15.

I have spent quite a bit of time trying to get this one right, even replacing it with IF statements, but I can not get the total right. Any assistance would be greatly appreciated.

Regards,

Ellen

[sunny_talwar]

Would you be able to share a sample?

[ellenblackwell]

Thank you for responding. I can not provide anything more than I have included in the question. Data and related details are confidential. Basically, I have to calculate a figure based on the following business rules:

     if DataSource = A, use Actual

     if DataSource = B or C, use Actual$-Target$

But, sometimes the data selected contains values from  A,B, and C. In this case, the total needs to equal:

     sum(Actual$) where DataSource=A

     +

     (Actual$-Target$) where DataSource=B

     +

     (Actual$-Target$) where DataSource=C

[sunny_talwar]

Can you make up data? and mention what would be your expected output from your made up data?

[Anonymous]

Dear Ellen ,

I can see there in your expression you are having ,

- For A,B,C  your are having data in [DollarsActual],[DollarsTGT] but with same flag .

- If you select ABC it will calculate including all the parameters . As of my assumption its showing the include value .

AsFor better understanding post a sample app or data.

Hope this helps .

Thanks ,

Bunny

[swuehl]

Can you tell what each single sum() contributes to your final result under your different scenarios?

What you see could be caused by the target sum() contributing the same value regardless if you select B, C or both, while the actual sum() are more selective.

It's hard to tell you more without knowing more details. Could you at least describe your data model a bit more detailed (by posting the table viewer and / or some conceptual explanations)?

What if you use

sum({$<FLG={'1'}, DataSource={'A'}>}[DollarsActual])

+

sum({$<FLG={'1'}, DataSource={'B','C'}>} [DollarsActual] - [DollarsTGT])

{which could cause unwanted record duplication}

or

sum({$<FLG={'1'}, DataSource={'A'}>}[DollarsActual])

+

(sum({$<FLG={'1'}, DataSource={'B'}>} [DollarsActual]) - Sum($<FLG={'1'}, DataSource={'B'}>} [DollarsTGT]))

+

(sum({$<FLG={'1'}, DataSource={'C'}>} [DollarsActual]) - Sum($<FLG={'1'}, DataSource={'C'}>} [DollarsTGT]))

Looking at your above post:

"But, sometimes the data selected contains values from  A,B, and C. In this case, the total needs to equal:    

sum(Actual$) where DataSource=A    

+

(Actual$-Target$) where DataSource=B    

+

(Actual$-Target$) where DataSource=C"

I think this really may be the problem, a set modifier like DataSource ={'B','C'} is using OR semantic and creating a union of both sets, where you seem to expect a separation of the sets, separately aggregating each filtered set. Probably most similar to my last suggested formula.