# Webi formula: Create sum with where clause on measure

Hi,

I am trying to create a sum of all values in a table which are greater than a specific threshold. Maybe someone can help me here. I haven't figured whether and how this is possible.

Here is a simple example:

DimensionMeasure
A12
B8
C25
D3
E11

I would like to sum up all values that are greater than 10. So I would have 12 + 25 + 11 = 48.

The formula I tried in WebI was:

=Sum([Measure]) Where ([Measure] >= 10)

The formula is validated with no errors, but the result gives me the grand total ignoring the where clause.

In this example, that would be 12 + 8 + 25 + 3 + 11 = 59.

Any idea, how to achieve a formula like the above?

Thanks and regards,

Stefan

##### Carly Thomasreplied

Hi Stefan,

If you have aggregation set on Anzahl measure at the semantic layer (Universe) or data source level, this might be why you're seeing a different.

Try:

=Sum([Anzahl] In ([Cluster]) Where ([Anzahl] In ([Cluster]) >= 90))

This will override the default aggregation you may have to avoid summing BEFORE analyzing your WHERE clause.

I hope that helps!

Carly

(had to edit, realized I left in my testing #s for eFashion Sales Revenue)

0 View this answer in context