if-statement expression not aggregating correctly

[rothtd]

Can someone help me with a percentage calculation that is not aggregating correctly? My expressions are using if-statements to filter out rows in a chart accordingly. [DPC %] is calculating correctly for each individual line in the chart, but the total for the expression is wrong. The total for [DPC %] is including rows in the chart are filtered out, so it is ignoring the if-statement. I believe this is because my expressions use sum(x) inside the if-statement – but I don’t know how else to do this expression. Any ideas?

Expressions:

Net Sales:

=if(vSalesFilter_UseTheFilterFlag='Yes', 

    if(sum([Net Sales]) >= vSalesFilter, sum([Net Sales])),

    sum([Net Sales]))

DPC $

=if(vSalesFilter_UseTheFilterFlag='Yes', 

    if(sum([Net Sales]) >= vSalesFilter, sum([DPC])),

    sum([DPC]))

DPC %

=

if(vSalesFilter_UseTheFilterFlag='Yes', 

    if(sum([Net Sales]) >= vSalesFilter, sum([DPC])),

    sum([DPC]))

/    

if(vSalesFilter_UseTheFilterFlag='Yes', 

    if(sum([Net Sales]) >= vSalesFilter, sum([Net Sales])),

    sum([Net Sales]))

[#2]

Try this.

[#3]

Using set analysis, it shouldn't matter how many records you have. What part is not working?

[rothtd]

If you think through how the expression total is evaluated, it first looks at the if-statement/s (which are true) then evaluates the sum(x) portion. At this point it would be a sum of all records, meaning the if-statement is negated. Thus, the rest of the chart works great - but the expression total is always a sum of all records regardless. This is why I think I need to embed the if logic within the sum statement, but I can quite figure out the best way of doing that.

[rothtd]

Thanks rebeccad - I think this works with the example data because of the small number of records. When the data scales up to millions of rows this solution does not work.

I think I need to put the aggregation outside the if statement (or use a set expression instead), but I can get it to work.

[#6]

That was why I renamed your Net Sales column to Net Sales  (added one extra space) and then used your column headers as the expression:

[DPC $]/[Net Sales ]

This way it will only evaluate based on the sums in the two columns.  Did you try that? Sorry, I guess I should have made that more clear.

[rothtd]

That's a really good idea. I couldn't get it to work however. It is still calculating based on all rows vs. only the visible rows. I added a 2 on the column names instead of your space to make the rename more visible.

DPC% 2 expression:

=[DPC $2] / [Net Sales 2]

[#8]

Can you upload a sample file with customer data scrambled out? I tried a test document with a ton of records and it was working.

[rothtd]

You'll notice on this version that when the filter is turned off it works fine, but when the filter is turned on it does not work.

Filter off:

the DPC%2 total should be 33% (1,135,207,375 / 3,462,945,698 = 0.3278)

Filter on:

the DPC%2 total should be 26% (282,682,342 / 1,077,512,423 = 0.262)

[#10 · Accepted solution]

See if this works.